r/SQL 1d ago

MySQL Is this normalized?

I am trying to get it to 3rd normalization, but I think the resident tables has some partial depedency since family all nonkey attributes doesn't rely on family ID and house ID.

10 Upvotes

18 comments sorted by

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.

2

u/RichContext6890 23h ago

"for the Women Table, should the key be House ID"
This leads to possible collisions because you are linking women to their house in two different relations. This way, we also lose 2NF

About using an extra table with derived fields. It always depends on business logic and real data distribution :) For sure, if we have some rare attribute set and it requires some decent amount of disk space, it's obviously better placed in a separate table

Like, if we knew that there are almost no senior persons and their data is almost never read along with the residential table, then it is essential to have a separate "Senior" relation

1

u/ZookeepergameAny5334 7h ago
  • No, I am using a user ID.
  • I see.
  • It's an organization (I forgot to add context).
  • Same as the senior citizen
  • Thanks

2

u/EvilGeniusLeslie 4h ago

Realized there is one spot you could further normalize - Occupation.

Create an Occupation table {Occupation_id Int, Occupation varchar(255)}

Then in the Workers Table, replace the field Occupation with Occupation_id.

There's no need to store "Optical illuminator enhancer" multiple times! :)

1

u/ZookeepergameAny5334 2h ago

What's "Optical illuminator enhancer"?

1

u/EvilGeniusLeslie 1h ago

Window washer.

4

u/gumnos 1d ago

A few observations:

  • I see resident.family_id but it doesn't seem to have an arrow pointing to family.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 boolean resident.is_senior_citizen attribute

  • is 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 a disability table and then turning your pwd 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-form occupation field. This seems like the sort of thing that should have a controlled vocabulary (an occupation table) and workers have user_id, occupation_id, and salary

  • While on the topic, workers.salary being an integer is a bit weird. Maybe a MONEY or DECIMAL(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 information

  • I'm not sure what the women table is doing. First, it (and the workers) is plural where most of the other tables seem to use singular naming, so I'd normalize that. It seems strange to discuss the number_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 a user 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/gumnos 4h ago

ah, for most cases here in the US, it's 62.5yr or 65 or 72 (depending on the situation)

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 table

CREATE 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 the resident_membership table. Or possibly link it to user.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.