CODEWITHSUNDEEP
sql
Beta033
Beta033

Reputation: 2013

What is the best way to tie 1 table to many tables

I have a table. Call it TableA

this table will link to many tables and ideally be enforced by database relationships in (many-1)(TableA-TableB) (many-1)(TableA-TableC) ... etc

The solution i have is to put all the foreign keys of TableB, TableC, etc in TableA along with a "Type" field (which contains a word version of which relationship is to be enforced). however i think there must be a better way. What would you do?

I'd appreciate any advice in this and thanks.

Upvotes: 0

Views: 99

Answers (2)

danben
danben

Reputation: 83250

This is a perfectly acceptable approach - foreign keys are indeed the correct way of modeling a many-to-one relationship.

Generally, you can't just say you want to make a solution "better"; rather, you should have a specific goal in mind. Faster, shorter implementation, less memory, whatever. Even better is if you have a specific use case you would like to optimize for.

Edit: your question is more clear now that you've edited it. If I understand correctly, you feel your current implementation is inefficient because one of your TableA items can be attached to at most one other item, be it from TableC, TableC, etc.

If that is correct, what I might do is implement the foreign key in Table A as both an ID and a table name, rather than having a new column for each new type of object you want to add to your system. Of course, this would prevent you from changing table names, so a more robust solution would be to have another table mapping unique ids to object types (stored as table names). Then the foreign key in Table A would be item_id and object_type_id, and you could retrieve the object by looking up object_type_id in the object_types table to get the table name.

Upvotes: 1

Jeff S
Jeff S

Reputation: 7484

If you want to add referential integrity enforced by the database server, each key must be represented by a unique column in TableA.

It's hard to give more advice than that without knowing more about what your design is attempting to do.

Upvotes: 0

Related Questions