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

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.

Why should the key columns be unique? Suppose TableA is "items" and TableB is "categories" - putting a unique constraint on the "category_id" field of TableA would disallow multiple items from belonging to the same category.

danben 2010-02-02 16:35:58

@danben: FKeys are used to enforce a 1-to-many relationship. If you want a many-to-many relationship, they wont do. Also ideally, you would create a middle table that would define the many-to-many relation, each line of the middle table being a relation.

kurast 2010-02-02 16:43:37

@kurast: the OP specifically states that he is implementing many-to-one relationships. I don't see the relevance of this comment.

danben 2010-02-02 16:45:45

@danben: well, now i see that when you said "Why should the key columns be unique?" it is a rethorical question, but when i first read it, i thought it was a doubt...

kurast 2010-02-02 17:23:13

@kurast: no, it was not rhetorical. I was asking Jeff S why he thought the key columns should be unique in this particular problem that we are discussing in this thread.

danben 2010-02-02 18:07:53

@danben: I probably should've used distinct instead of unique. I'm not saying to use a unique constraint; that won't work. I'm saying that, in order to have referential integrity enforced by the database, each reference must be in it's own column.

Jeff Siver 2010-02-02 18:16:29

@Jeff S - ah, ok. I think that that's what the OP has now, though.

danben 2010-02-02 18:30:45

My present way is most definately inefficient. However i've thought about this and unless i'm mistaken, this technique breaks refenterial integrity to the objects that are supposed to contain the Item_id

Beta033 2010-02-02 21:47:06

@Beta033: I suppose. What problems do you see arising from this solution?

danben 2010-02-02 21:50:46

ansaurus

tags:

views:

answers:

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

related questions