开发者

Database: relating one type of record to multiple other tables

What is the best way to relate one type of object to one of multiple types of other objects?

For example, a simplified scenario:

tblNews (_newsId_, _title_) 
1, Hello world
2, Lorem ipsum

tblPeople (_personId_, _personName_) 
1, John Smith
2, Joe Bloggs

tblPlaces (_placeId_, _placeName_)
1, Townville
2, Smallplace

What would be the best method for defining relationships between the items in tblNews and both people and places (e.g. news item newsId=1 (Hello world) could be related to person personId=2 (Joe Bloggs) and place PlaceId=1 (Townville))? I have considered using two tables as in:

tblObjectTypes (_typeName_, _typeTable_, _typePK_)
person, tblPeople, personId
place, tblPlaces, placeId

tblNewsRelationships (_relationshipId_, _newsId_, _objectType_, _objectKey_)
1, 开发者_运维技巧1, person, 2
2, 1, place, 1

Here tblNewsRelationship defines which news item is in the relationship, which type of object it relates to and the primary key of that object.

tblObjectTypes contains each type of object that the news item could relate to, which table that kind of object is stored in and which field should be used to look them up. The code would go to tblObjectTypes, look up the objectType defined in the relationship and use the typeTable and typePK given to find the correct record given its objectKey.

However, this seems awfully convoluted for what seems like it should be a common problem. I've searched and searched, but without knowing the terminology describing this type of relationship it's impossible to find anything useful so I have no idea if there's a 'correct' way of creating this sort of relationship.

Sorry for asking such a long question.


A news item can be related to a person and a place, right? So the news table should have a column called person_id and a column called place_id. Each news row will have the appropriate id number of the corresponding person or place.

"Foreign key" is the term you want.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜