Good db table design: one table mixes different objects or a separate table for each object

Question

What is the best database design?

Having one large table that can contain different "types" of records, for example: employees, cars, cell phones, and to identify each type of record, we have a column named type.

So the table will have columns that look like

id | type | name

1 | car | Ford

2 | car | Toyota p>

3 | phone | Motorola

4 | employee | Jack

5 | employee | Aneesh

6 | phone | Nokia

7 | phone | Motorola

or have different tables for each type

eg:

Staff

id | name

Cars

id | name

Phones

id | name

These tables may have links to foreign keys from other tables. Now, if there were different columns in each table, the solution would be simple that you cannot have this in the same table. So option 1 is probably excluded (if all columns that are not shared are NULL). But what if these different objects had the same columns, in this case the best design?

What could be the arguments for and against everyone?

Answer 1

I agree with everyone - definitely use separate tables. You have nothing to lose by having separate tables — just because you have a few more tables, your database will not become slower or less manageable.

But you win a lot - you do not need to have many fields that do not make sense for one type of entity, etc. You stick with 2NF, as many have indicated, and this is definitely a good thing!

Check out this interesting article on Simple Talk entitled

Five simple database design errors and how to avoid them

Mistake # 1 is what the author calls a “common lookup table,” which is very similar to what you are trying to do, but for real live data.

Read the article, learn all its requirements - great things and highly recommended!

Answer 2

Since they are really different types, I would suggest storing thedm in separate tables. This eliminates the need to maintain a list of types, as they are all in their own tables. In addition, if one of these types is expanded in the future (for example, you are going to store phone numbers for employees), you will not get any strange relationships, such as phone numbers for cars. It also makes it easier to understand and maintain your database.

Answer 3

Roald van Doorn is absolutely right. If you have one table and you expand it in any way, you will break the second normal form . As William Kent said, "The second normal form is broken when the non-key field is a fact about a subset of the key." The Royal Van Doorn example of “employee phone numbers” illustrates the violation.

William Kent A simple guide to the five normal forms in relational database theory is an excellent document to consider when asking yourself a question about database design.

Answer 4

I will say that all of the above answers are 1. correct, given the example given in the question, and 2. correct almost all the time.

Now I am faced with a situation where one table is better. It is so rare that when it appears, I wonder if I need to architect a monolithic (neutral) entity or not. I quickly reject the desire - perhaps by asking someone else, and I cannot correctly state my case, and we refuse this, as we always do.

Then, as it turns out, too late in the game I find out that I had to make one table of a neutral entity type.

Here is an example that makes my case:

Suppose there are two types of entities, a corporation and a person. A corporation is usually owned by a person, but sometimes another corporation owns the corporation.

Holding on to this thought and adding to it, let’s say that every corporation has a registered agent who is responsible for the legal establishment of the corporation. And, in addition to my illustration, a registered agent can be either a person or another corporation.

Given that the owner / parent of the corporation / child may be a person or a corporation, you can start considering the problem. On the contrary, if only people can own corporations, your ownership link table is very arbitrary with the columns: OwnershipID (sort of unnecessary), CorporateID, PersonID.

Instead, you need something like: OwnershipID, CorporateID, OwnerID, OwnerType And anyway you can do this work, but it will not be so funny if not to say.

Continuing with the example I gave, you need to assign an agent for each corporation. Usually the agent is one of the owners (person). In this case, you really want to associate yourself with one record of this person. You do not want to register a person as an owner, and then again as an agent (in the agent table). That would be redundant. Bad things will happen. :-)

Similar to this “problem,” the registered agent may also be a corporation, such as a law firm, CPA, or Biz Filings, causing some typical examples. Just like agent-man, agent-corporation really should not receive its own record. It should be linked to an existing record of its corporate existence in the "Corporation" table. [except that I ultimately say that I don't have a corporation table)

Like a link table matching each corporation with its owner (s) of any type, person or corporation, you can have an agent link table: AgentRepresentationID, CorporateID, AgentID, AgentType ... but again, it’s ugly (IMO) when you need to combine related agents — some from the Person table, some from the corporation table.

So instead, in this case, you can see how a neutral type of object can be beneficial. It will be something like this:

Table: EntityAll Key columns: EntityId, EntityType (or EntityTypeID, if you insist, a link to get a description), EntityName (there are problems with names and different types ... from topic to this post)

Reference table: CorporationOwnership Key columns: OwnershipID (again, my comment is that this is partly necessary), ChildEntityID (the object that belongs to, called "Child" for clarity, I would not call it) ParentEntityID (parent object)

Reference table: AgentRepresentation Key columns: AgentRepresentationID (... I won’t say), Corporation EntityID (representing the corporate entity), AgentEntityID (from the Entity table, equating to the record that the agent makes here)

While you can be fine with my architecture, you should be a little worried about the column names in the link tables. It bothers me. As a rule, the names of the second and third columns in these tables exactly correspond to the JOIN column names in each corresponding entity table (haha, but each object does not have a corresponding table, so you cannot have the column table names of the links table correspond to the source column names, since they are the same column). Technically, it doesn't matter, but it violates your naming conventions, which should be meaningful, but not enough to not.

In case I haven't brought him home well enough, here's how you do it. You are a JOIN EntityAll table for yourself to get what you need.

List of all cases and their owners (in T-SQL):

 SELECT Corp.EntityName as CorpName, Owner.EntityName as OwnerName FROM EntityAll as Corp JOIN CorporationOwnership as Link on (Corp.EntityID = Link.ChildEntityID) JOIN EntityAll as Owner on (Link.ParentEntityID = Owner.EntityID) 

Therefore, you would do the same to get the agent, not the owner (s).

I understand that we are not learning architecture, but I am very confident that my solution eliminates redundant data and simplifies coding, management and reading.

If you insist that I am wrong, let me know. Suggest how you archive my example with separate entity tables of the corporation and Person. Hooray!