Relation with different possible owners

Question

Relation with different possible owners

artiom.f

Newbie

Points: 3
More actions
April 22, 2010 at 4:08 am

#223735

Hello guys!
Pretty typical situation: need to store tags and comments of articles, videos, projects, announcements.
Of course I don't want to create different table for news' comments, videos' comments and so on.
I have thee design variants:
1. First option is to have field ownerId in comments, tags (there can be more entities) to store id of video, project or other owner and field BelongsTo to define owner type. BelongsTo stores id of OwnerTable from OwnerTables table.
Really lots of relations (with Enforce Foreign Key Constraint = no) between owners and owned. And you need to add them for every new owner or owned.
2. Here we have new table Belonings which stores OwnerId (project, video...) and owner type from OwnerTables (BelongsTo).
Owned tables stores BeloningId. No need to create lots of relations for every new owner or owned
3. Now we don't have OwnerTables table, but Belonings table has one column for each possible owner (those column can be null). Good point is that we can set Enforce Foreign Key Constraint =yes so cascade deletes and updates are possible. Scheme looks nice, but maybe storing loads of null values is not a best option...
What do you think?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 1

I suggest you create a parent "supertype" table for your Announcements, Videos, etc tables. Reference that single parent table from your Tag table. You will find examples of such supertype / subtype patterns in most good books on data modelling.

You ought to know that the word "relation" has a very specific and important meaning in relational database design but it is not what you are referring to here. Relation is the stem of the word "relational" and it means the mathematical object that corresponds (more or less) to a table in SQL. It has nothing to do with foreign key constraints or semantic relationships between data in different tables.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

artiom.f (4/22/2010)What do you think?

I'll love to see an ER diagram as well as business requirement in terms of how data is expected to be retrieved from the database.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 3

PaulB-TheOneAndOnly (4/23/2010)
I'll love to see an ER diagram

But please not ones like these created with the Management Studio "designer". Silly pictures like that don't even qualify as an ER diagram in my book. They don't follow any of the industry standards used for logical diagrams and they show so little information they are useless for any serious design work.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

David Portas (4/23/2010)
PaulB-TheOneAndOnly (4/23/2010)
I'll love to see an ER diagram
But please not ones like these created with the Management Studio "designer". Silly pictures like that don't even qualify as an ER diagram in my book. They don't follow any of the industry standards used for logical diagrams and they show so little information they are useless for any serious design work.

Agreed. I'm referring to the actual ER diagram created during design phase - the one you sketched in a notepad during interviews and finetuned afterwards.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

artiom.f Newbie Points: 3 More actions · Answer 5

David Portas (4/22/2010)
I suggest you create a parent "supertype" table for your Announcements, Videos, etc tables. Reference that single parent table from your Tag table. You will find examples of such supertype / subtype patterns in most good books on data modelling.
You ought to know that the word "relation" has a very specific and important meaning in relational database design but it is not what you are referring to here. Relation is the stem of the word "relational" and it means the mathematical object that corresponds (more or less) to a table in SQL. It has nothing to do with foreign key constraints or semantic relationships between data in different tables.

Could you please give me some link where I can read about it?

artiom.f Newbie Points: 3 More actions · Answer 6

PaulB-TheOneAndOnly (4/23/2010)
David Portas (4/23/2010)
PaulB-TheOneAndOnly (4/23/2010)
I'll love to see an ER diagram
But please not ones like these created with the Management Studio "designer". Silly pictures like that don't even qualify as an ER diagram in my book. They don't follow any of the industry standards used for logical diagrams and they show so little information they are useless for any serious design work.
Agreed. I'm referring to the actual ER diagram created during design phase - the one you sketched in a notepad during interviews and finetuned afterwards.

I thought some typical design exists, because situation is very common...