In the end, database design is an exercise in the abstract. Its taking very concrete concepts like:
Product X can be blue, red, or yellow
Product Y can be orange, gray, white
And then figuring out how to create a database that can help users select from the available options. To do so, you need to move from concrete to the abstract. Otherwise, you get an ugly database where you have a table "tblProduct" with check box fields for each possible color, and bunch of awkward code to make it work. But by thinking of the abstract concepts, which isn't always an easy feat, it's clear you want to actually have a separate table for color. Then, because each product may have more than one possible color, you have to create another table where you can marry them together.
So what's the tip for the day? When thinking of how to architect your database:
- If you think of the "thing" in the plural, it's probably a table unto itself. Products, customers, contacts, notes, colors, etc. In some cases it's just a simple lookup table.
- If you are naming the field and you label it in the plural, you may be describing a many-to-many relationship as opposed to just an attribute of the thing your table contains (as in the example we started with where you're tracking Products, if you started to name a field "Possible Colors," it probably deserves some reconsideration as just a field in your Products table).
- Try to avoid creating separate tables just to differentiate the state of something. For example, if you want to track Customers and Prospects, and all the fields are the same, it's highly recommended that you keep them all in a single table, and have a field that refers to each records' state (customer or prospect). You can then filter your forms, queries, and reports if you need to show only one. Plus, if someone changes from a Prospect to a Customer, it's a single field value that changes. No messy transfer of data from one table to another to signify the change of status.
Happy New Year!!!!