A few database design questions


If I have a customer table, it will store name, address, email address, phone number, and perhaps even some details about the customer like age, preferences, etc.

Would I be doing a good thing if I partition this into smaller tables? E.g. customer_contact with the contact fields, and leave just the name, date of birth, etc in the original Customer table.

Also, with lookup tables, they are just a combination of fields from separate tables into one large table, right?

Also, in my own systems, I have a table representing a product but it all it has is an ID. The only field of this table is a field/attribute which applies to many products (like if it is road legal), and this is a field of another table, so there is a constraint between both tables (Relationship). I would assume a lookup table would be merging these two tables together, right?


Best Solution

In most cases it is generally better to decompose. Certainly in the case of everything that you listed.

Try to think of your database design like an OOP program in a language like Java, where complex objects are linked. Anything that could be "linked" to your entity, especially if it could be linked to multiple entities, may be a candidate object and thus a table.

Give the primary customer table only the core information about the individual that is necessary to identify him like you suggested.

Then all other metadata and auxiliary data can be tied to it. For example, addresses or phone numbers or emails are good object candidates deserve their own table, especially since they may have additional properties. Another table can then associate addresses with customers (e.g., what if you have a whole family using your system).

Related Question