3

and thanks in advance.

I am trying to normalize a table "People", where each entry (person) belong to one of many different categories (e.g. "lawyer", "client", "judge"). Some fields are common across categories (e.g., firstName, lastName).

However, some (set of) fields are unique to each category: for instance, a "client" will likely not have a "LawyerBoardID" number. Therefore, an entry will have a value in that field if and only if the person belongs to the "lawyer" category; in other words, that field will be NULL for all other categories.

This has implications for child tables. One person, many entries in the "Address" table - textbook example. If I split my "People" table in many tables - namely, one per category - then I will have to put as many FK in the "Address" table, right? Therefore, if I have 10 categories, there will be 9 NULL FKs.

Any suggestion? Links to relevant web pages are also very welcome.

1

Your case looks like an instance of class/subclass (or, if you prefer, type/subtype). You can find many answers like this one in SO or here in DBA.

1

Don't replace people with a separate table for each person. You may want child tables for types that carry type specific data. These will have the same primary key as the people table, but only exist if required.

A lawyer may have a law office address which is specific to being a lawyer which would be stored in the lawyer subtype table. Follow this pattern for other subtypes.

Consider whether a person can be of multiple sub-types and how to handle that case. Some of the sub-types appear as if they may result as a relationship to a different entity such as case. Consider if the subtype fields are really relationship fields. Join tables can contain additional fields.

0

It sounds like you may want to consider one of the higher normalizstion forms and use views to get at your data. However, you need to think about how you use the data in your database so that you can dwsign a schema. Are you doing data watwhousing? If so, normalization can work well. If your doing a lot of reads accross different schemass and tables all the time you might consider a document store.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.