The Lanham Factor

My take on this is that if two tables share a common key AND they are 1::1, then the fields from each table should be combined into a single table.


E.g. given the following

Universal Field List (Table "U"): Has fields for All clients

Custom Field List (Table "C"): Has client-specific data elements


Option 1 - Two Separate tables (U 1::1 C)

In this option, U is the same for all clients, C must be managed and tracked independently for each client. Adding features in future version for all clients involves modifying table U.

Option 2 - One Single Table (UC)

In this option, UC must be managed and tracked independently for each client. Adding features in future versions involves modifying table UC.


Summary: No matter which option you choose, you will still need to track features by client and features for all clients still requires the same amount of design work. You do not avoid this by making two tables. In fact, adding the second table complicates things like changing keys and reporting. How do you add a new row? To add a row to the first table, you'll need an existing row in the second table to enforce 1::1. You can't add a row in the second table unless there is a matching row in the first. Kind of a chicken-vs-egg issue. In practice you would probably end up writing a view which combines both tables anyway so just keep things simple and create a single table.

Just my humble opinion.

I totally agree. I guess I was just hoping to keep the core schema more pure. But, on another note, it prompted me to be curious about how organizations who have fixed core tables extend them. Then I realized my brain-fart...data abstraction with views and stored procedures. I'm such a dufus. Thanks!

Using the UC model above:

If the C table contains fields that are dynamic, or change with each client, then you could have a single row per attribute, right? I mean, so long as each attribute is a single field anyway, you could add a very dynamic system using this model. True, this adds some difficulty managing a one to many (i.e., the join would be become sort of pain and require dealing with the duplicate rows.)

I guess it really depends on how important it is to you to keep the original schema clean. I've extended tables this way, and I've seen many databases that worked this way. It *does* work, and it is clean. However, the coding on the front-end can be much more intensive.

Just my .00005 cents.....

That's an interesting thought. Since the final result will be all in Access the coding can be easier or tougher! :)