A few database design questions

Question

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?

Thanks

Answer 1

It depends.

The customer_contacts table would be of use when there is a possibility of having multiple (unknown) number of rows of contact for each Customer. On the other hand, if you are sure of having 3 contact details per Customer, you can store it in the same table as that of Customer.

Answer 2

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).

Answer 3

Generally speaking, you would only (vertically) partition tables like that if you have a HUGE number of columns (in which case, a re-design is probably in order), or if you have different SECURITY requirements for different data (SSN or salary data separated from normal data).

When you say "lookup table", I think you are actually referring to "foreign keys". If you have a table that holds product availability, then each row would have a ProductID that points back to all the rest of the product information.

Answer 4

The term lookup table is overused, generally. If you think again in terms of standard programming, a lookup table is the equivalent of using constants to refer to magic numbers or constant objects.

Hence, you use a unique identifier for another entity that is typically atomic in that it does not contain other objects (e.g., state, address, product details, etc.). In the core table, you would have the ID instead of the actual details.

If a table refers to a central entity, it's better to think in terms of relations rather than lookup tables.

Answer 5

I think Database design is all about balance and judgement. If you can't see the database getting very big then normalize it. If you can see it growing quite large then IMHO hold back on the normalization unless its necessary IE don't use mapping tables everywhere cause no matter what anyone says a flattened database runs faster.

I would store the address in the same table unless you feel there is a possibility of the client wanting an address history or separate billing and shipping address. I wouldn't ever break the contact details and birthday because their isn't really a point.

I use lookup tables like enums and in fact most of them become Enums.

Everyone has there own ideas about database design......

Answer 6

It's a bit of a balancing act, having all your columns in one table (denormalized) will result in fewer joins and better performance, but will be a pain to maintain if you have to change things later. As Uri mentioned, thinking about your database design from an OOP perspective will help you establish which tables should be independent. I would highly recommend learning how to put together a simple Entity-Relationship Diagram. This will allow you to map out your database design and work out how everything will link together before getting too carried away with implementation.

Answer 7

You're asking the right questions. The concept of dividing your data into reusable tables is called "normalization". Typical customer relationship manager (CRM) systems have some tables like Phone, Address, Person...very generalized tables that can be re-used for various purposes.

For example, Phone and Address can be used not only for customers, but for shippers, suppliers, employees, etc.

Once you have a basic structure, you can then start linking Customers to Addresses and Phones. And remember, each customer can have a ShippingAddress, BillingAddress, HomePhone, BillingPhone, MobilePhone, etc. You'll be creating tables like CustomerAddress and CustomerPhone to match customers to their respective info.

Answer 8

Somebody much wiser than me once said: "normalize until it hurts, denormalize until it works"

It is wise to break a long table into smaller semantic chunks that are joined through one to one relationships. You could then call them through a view. Even many ORMs are View friendly

However, these extra joins will hurt you if your database gets many hits, like it would do in a web or intranet scenario.

If you want to keep your tables separated in a high stress scenario you might want to use a dirty and ugly cheat that is used in many public web projects and create a fake view by delegating a column on the main table for storing the related data in a collection.