CODEWITHSUNDEEP
databasems-access
SSK
SSK

Reputation: 803

Why can't we put all masters in one Master table in Database?

We are designing a small database in MS Access 2010 and we have like 3 master attributes Lets take for example we have Country, State and Tastes. Instead of designing master table for each attribute, we have come up with one table like below

ID  Value       Attribute
1   USA         Country
2   UK          Country
3   Illionis    State
4   Wisconsin   State
5   Sweet       Taste
6   Sour        Taste

We are using self joins and getting what is required. Does anyone think that, it is not a good database design, if yes please explain

Upvotes: 1

Views: 180

Answers (2)

Bernhard Barker
Bernhard Barker

Reputation: 55619

Reasons against:

1) Extra storage space to store a field indicating what type it is (cancelled out by the primary keys on each table when having multiple tables, but then you'll need to store the type as an (small) integer type, not a string type).

2) Extra storage space for fields that are not applicable to certain types (N/A if the above is not just an example, and there won't be more fields, but then I'm questioning the rest of your DB design, and extensibility is always worth a consideration).

3) Reduced performance to select the applicable rows.

4) An index would obviously be required on Attribute (otherwise (3) is a performance killer), so - reduced performance on update and delete statements.

5) Bad database design - don't combine concepts that don't belong together

EDIT:

6) Database integrity - what stops you from just inserting invalid data into the Attribute field. Admittedly, you can have another table with attributes and make Attribute a foreign key to that table, which is a bit messy and confusing to figure out what's going on sometimes.

7) Foreign keys - doing this will just be a mess, not too mention you can't enforce database integrity and likely speed implications.

8) Visualization - any table diagrams will have to be manually drawn or edited because an automatic generating tool (most likely) won't be able to account for this type of design.

Upvotes: 1

Colin Young
Colin Young

Reputation: 3058

If I need to get a list of states by country, how do I do that? With your design, you can't do that, other than by adding an additional table. If you split into entity types, e.g. Country, AdministrativeDivision and Taste, you can store the appropriate attributes per entity, instead of complicated join tables. The resulting SQL is easier to read and debug.

There is really no reason to attempt to "optimize" by minimizing the number of tables. Any modern database engine will not suffer a performance penalty from additional tables. Your design may in fact trigger a performance penalty. Depending on how many different entities you try to jam into that table, you may end up making it so large that the entire table can't fit into memory, thus forcing the database to page from disk when performing selects and joins on this table. A good rule of thumb might be if you can't make a reasonable guess about what query plan your database might use to get the data you are requesting, just follow accepted SQL best practices.

There is one situation I can think of where this design could be acceptable, and that's if you need to provide a store for users to add their own categories and values at runtime.

Upvotes: 0

Related Questions