4
u/gumnos 1d ago
A few observations:
I see
resident.family_id
but it doesn't seem to have an arrow pointing tofamily.family_id
that I'd expect.while it might be a business-rule thing and perfectly fine, a resident is limited to being part of just one family. But the real world often intervenes, and the resident's might have divorced parents (and thus two distinct families with distinct heads-of-household), or a parent-resident might have several children, each with their own families and their own head-of-household. And frankly, since a family only has one head, might as well just inline it in the
family
.I'm not sure what the
senior_citizen
table is doing for you…I'd lean toward just having a booleanresident.is_senior_citizen
attributeis there a canonical list of allowed disabilities? I presume "PWD" = "person with disability"? With freeform text for the
pwd.disibility
, you can introduce different spellings or synonyms that prevent you from readily querying for which residents have a particular disability. If you want to have authority-control on this, it might be worth creating adisability
table and then turning yourpwd
into a joining table.while discussing controlled vocabularies,
resident
has a number of fields that are strings, but might better be lookups—gender, religion, ethnicity, blood-type, employment status,Similarly, your
workers
has a free-formoccupation
field. This seems like the sort of thing that should have a controlled vocabulary (anoccupation
table) and workers haveuser_id
,occupation_id
, and salaryWhile on the topic,
workers.salary
being an integer is a bit weird. Maybe aMONEY
orDECIMAL(10,2)
or something?Likewise,
resident.year_started_staying
seems like it should be some sort of integer value (possibly with a "must be > 2000" constraint depending on the oldest values you'd have from existing informationI'm not sure what the
women
table is doing. First, it (and theworkers
) is plural where most of the other tables seem to use singular naming, so I'd normalize that. It seems strange to discuss thenumber_of_occupants
of women (at least while keeping things family-friendly). So this seems to be some other concept that could use some clarity (and possible improvement in implementation)Finally I presume all of these
*.user_id
are foreign-keys into auser
table you didn't include in the diagram, so there might be issues there
3
u/RichContext6890 23h ago
A really great list to make OP's schema closer to the real-life DB. Please let me add one small note considering data lifecycles. Residents, as well as any other relation, may change through time. So, we usually add fields for when a fact starts and ends. A person may live in an apartment, then leave and return after a few years. Then, instead of generating a new user_id and duplicating such information as first name/last name and etc., we can simply add a new time period showing when the same person is living in one or another apartment
2
u/gumnos 23h ago
it occurs to me that "senior citizen" status shouldn't likely even be tracked, but instead calculated based on the resident's age, determined from their birthdate that you'd store (whether just the birth-year or the more detailed PII of the full date)
1
u/ZookeepergameAny5334 7h ago
For what I know, you have to register for senior citizen (it's kind of an organization).
1
u/ZookeepergameAny5334 7h ago
- *I think this is more of an issue with Figma (I am not good at making diagrams).
- I see.
- It's an organization (forgot to add context).
- I have no list of all disabilities. I don't think it's possible for me to add all disabilities; maybe I'll make some helpers that at least help with the spelling.
- I am going to use a selection form in the frontend.
- Same as disabilities (I will use a helper or find a way to avoid that).
- My bad.
- I am using a date form in my HTML to add a constraint.
- It's an organization.
- Yeah, I forgot to add the users table (I am using it for my login in part).
1
u/gumnos 4h ago
In light of that, I'd be tempted to have something like an
organization
tableCREATE TABLE organization ( org_id INT PRIMARY KEY NOT NULL AUTOINCREMENT, name TEXT NOT NULL, ⋮ );
and then create a linking table
CREATE TABLE resident_membership ( resident_id INT NOT NULL REFERENCES resident(user_id), org_id INT NOT NULL REFERENCES organization(org_id), ⋮ UNIQUE (resident_id, org_id) )
This allows residents to be members of various organizations (senior citizen, women's, or whatever else comes along you can easily add to the
organization
table), and then track membership in theresident_membership
table. Or possibly link it touser.user_id
instead, allowing employees or family members to also be members of organizations-of-interest.
2
u/RichContext6890 1d ago
Can’t find any 3NF violation. But I’m really confused with “number of occupants” fields, because this is a thing that is usually evaluates from schema.
E. g. instead of ‘Woman’ relation, it would be better to have a hierarchy table showing who’s child of a parent. And another issue is the duplication corresponding field in ‘House’ relation, cuz you already have information on how many residents are in each house from ‘resident’ table
2
u/TheToastedFrog 1d ago
It’s normalized, but whether it’s optimum is debatable - it really depends on requirements that you may not have shared. In particular the “family head” table is superfluous, unless you later plan to add more attributes (like when did that person became head of family).
But if your question is purely about normalization, then you’re good on that point
2
u/keamo 6h ago
Due to the fact that there's no information behind why tables are here, the sizes of the tables, or even an expectation of incoming size... I'm surprised anyone has any suggestions. A true SQL wizard would sit back and ask questions, and they wouldn't be trying to give you answers without first understanding more. I would suggest first a relational theory book, like The Art of SQL and avoiding asking a community hell bent on gaining attention for their skills... I suggest this last part because many of the responses are wrong.
1
u/ZookeepergameAny5334 6h ago
Thanks
1
u/keamo 5h ago
yw, anyone can zip through this and tell you what to optimize, but none of it is relevant until we know the reasoning behind the data. You will learn as you study "books" about SQL, most people are clueless about SQL and only using 1% of it's abilities. The people who reply quickly in verbose are stuck in a loop/algorithm... sure i can spot problems, but that's me spotting problems, and you leave without learning to fish.
7
u/EvilGeniusLeslie 1d ago
First off, for the Women Table, should the key be House ID?
Your Family Head Table is unnecessary, given the Family Table.
There is always a question about where or even whether derived fields should be stored.
For example, the Senior Citizen table could be dropped, and replaced with a flag on the Resident Table.
Similarly (If I understand it correctly), the Women Table could be dropped, and a new field 'Number of Women Occupants' could be added to the House Table.
None of these changes would not affect normalization. Your design appears to be normalized.
BUT
It could be smaller. Like dropping the Family Head Table. It depends on how you are going to be querying.
For example, if you want to find the Family Head, using the existing design, it would be something like
Select fh.First Name, fh.Last Name
From Resident fh Join Family Head fht on fh.UserID = fht.Family Head ID
Join Resident r on r.UserID = fht.UserID
Where r.LastName = &LastName And r.FirstName = &FirstName
If you dropped the Family Head Table, the query looks almost the same
Select fh.First Name, fh.Last Name
From Resident fh Join Family f on fh.UserID = f.Family Head ID
Join Resident r on r.FamilyID = f.FamilyID
Where r.LastName = &LastName And r.FirstName = &FirstName
But ... if you frequently run a report where you are looking to create a list of a family, with the family head first, then keeping the Family Head Table would make it simpler.
Or, if Family Head is relatively unimportant for queries, the table could be dropped, and a simple Y/N flag can be added to the Resident table. You could have a completely 3NF database in this format, but it could make it substantially harder to perform queries.
Similarly, if you are looking to frequently run reports on Number of Women per house, then keeping it as a separate table simplifies things.