0

I'm designing a database where I'll have some data that has basically the same structure in multiple places, and I'm wondering if I should make it in 1 table or multiple.

For example: I have an Apple, Orange and Banana table, Each row in that table is associated with a "Basket" and of course it would not do to have a an Orange associated with an Apple basket. As far as the Database is concerned the Basket types will have the exact same structure.

So which would be better

a: Make a Apple_Basket, Orange_Basket and Banana_Basket table?

b: A single Basket table that has an integer for type (that maybe goes to a basket_type table)

with a, I'll have a bunch of tables with the exacts same structure, but queries and constraints will be simple

with b, I won't need to add as many tables, but I can't really do a FK as easily since I need to constrain the basket to the right type, maybe an FK + Check constraint

Is there a name for this sort of "Parallel structure problem" and a best practice?

Thanks -AB

2 Answers 2

0

Plan B, of course.

If the only difference between the "baskets" is a type of items in the basket, then you can have just a "dictionary" for them.

If the items are really different (apples vs automobiles) and "apples" are described by one set of tables, while "automobiles" described by another set of tables, you can do it with a compound key:

create table item_types(
   type_id integer primary key,
   type_name varchar(128)
)

-- two completely independent tables:
create tables apples(id primary key)
create tables automobiles(id primary key)

create table basket(
   item_type integer references item_type(type_id),
   item_id integer 
)

item_id in this case will point either to table apples or automobiles so it cannot be a simple FK, but you can do check with as much complicated function as needed.

0

It depends.

As you note, both solutions have tradeoffs and you must pick one better suited to specifics of your case.

I am leaning towards the solution A ("Make a Apple_Basket, Orange_Basket and Banana_Basket table"), unless you expect there to be a LOT of such structures with only few records each (in which case you'll likely want to generate them programmatically).

Another thing to consider is: Will you be always querying single Basket at the time, or will you often query all/several?

I'll have a bunch of tables with the exact same structure

Unless you have a LOT of such tables, this isn't really a problem. If your DB can handle 100 tables (for various fruits), it can probably handle another 100 (for their baskets).

Also, while code deduplication is commendable practice for application programmers, the SQL isn't very well equipped for it and you'll often end up repeating yourself.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.