Problem with foreign key multiple table reference 1

[jgd1234567]

Hi, i have the following tables:-

NewsArticles (for my news section):
- ArticleID (PK)
- Message
...

Products (for my products section):
- ProductID (PK)
- Title
...

I need to add a comments facility to both my news and products sections. I don't wish to create seperate tables for each as i would then need to have two seperate ways of moderating these tables unless i do a union which is not particularly ellegant. Also i may add additional sections later and don't wish to add unions around my whole site where i wish to display both the products and news comments together. One solution is to create a table:

Comments:
- CommentID (PK)
- TableID (FK)
- Comment

But now i can't add a relationship to both tables.

I'm sure this is a common problem and would appreciate some help.

Thanks

 

[r937]

it's actually very easy --

Comments:
- CommentID (PK) NOT NULL
- ArticleID (FK) NULL
- ProductID (FK) NULL
- Comment




r937.com | rudy.ca

 

[chamilz]

another way..

NewsArticles:
- ArticleID (PK)
- CommentID (FK)
- Message
...

Products:
- ProductID (PK)
- CommentID (FK)
- Title


Comments:
- CommentID (PK)
- Comment

 

[r937]

chamilz, with that scheme, you would be able to have only one comment per product, and only one comment per article

r937.com | rudy.ca

 

[chamilz]

r937
yes, it is one-to-one. never thought that jgd1234567 wanted multiple comments for one product or news.

 

[babe1898]

Hi all,

I'm pretty new to SQL so I only know some of the basic stuffs. I created two columns BEGIN and END both have a TIMESTAMP as datatype. Now. my question is how could I get the diference between these two times/date, meaning the duration between the BEGIN and the END. For example is shown below

Begin
15-NOV-07 08.52.00.000000 AM

End
16-NOV-07 08.58.01.000000 AM

Could you please give some ideas or is/are there any functions that can be used? Thank you in advance.



Cordially,

Babe1898

 

[babe1898]

sorry for my last reply..i was supposed to crerate a new thread..sorry again.

 

[jgd1234567]

Hi cheers people, yeah i need the documents and articles to have multiple comments so chamilz design is not going to work. I'm not sure i like the alternative method though. Is there not an alternative way? I will look around and see if i find anything.

 

[r937]

the alternate method you refer to is the one i suggested?

what don't you like about it?

r937.com | rudy.ca

 

[jgd1234567]

Hi the problem is that news and products was just a couple of examples. I have lots more sections and therefore i would have lots of redundant fields in the comments table.

 

[r937]

why oh why do i always fall for the question-that-was-asked-wasn't-my-real-question trick!!!!

r937.com | rudy.ca

 

[jgd1234567]

Hehe, i tried to make my example as simple as possible. Sorry to confuse you.

 

[r937]

fortunately, my solution scales

your comments table would not have "redundant" fields -- there would be one foreign key for each of the tables that the comment could be a comment for, and on any given row, all of the FKs except for one would be NULL

what exactly is it that you do not like about this approach?

r937.com | rudy.ca

 

[ESquared]

Use the supertype/subtype design pattern.

Since you wish to relate to news articles and comments in a similar way and at a higher level than which type they are, you create a supertype table. The identity value of these moves to the supertype table. Here:

Currently you have this:

NewsArticles --(and may I suggest shortening this to just Article)
- ArticleID (PK)
- Message
...

Products
- ProductID (PK)
- Title
...

Now create a supertable. This one has the identity column (if that's what you're using):

Items (come up with a better name than this)
- ItemID
- ItemTypeID (FK to a new lookup table which has, say, 1=Article and 2=Product)
- Any columns in common between NewsArticles and Products, or could be treated similarly.
- For example, Title or something similar to Title probably exists for both.

Your two original tables become subtype tables: they lose their identity columns and get an FK column called ItemID (yes, even though it is in the NewsArticle table, although if you really wanted you could call it ArticleID... I haven't yet chosen a really strong preference between the two, there are advantages to each way). This FK column is also the clustered index. Now the relationship between Item and Article is 1-to-zero-or-one. The relationship between Item and Product is also 1-to-zero-or-one. The relationship between Item and (Article UNION ALL Product) is 1-to-one.

Get the idea? The article table's primary key value is not dense any more. If ItemID 1 is an article then you have ArticleID 1. If ItemID 2 is a product then there will never ever be an ArticleID 2. It's just "missing" from the table.

When you want to use Articles and Products as similar items, you select from the Item Table. You can FK reference the item table in your database. That makes your comment table look like this:

Comments:
- CommentID (PK)
- ItemID (FK to Item table)
- Comment

If you want to use something that is just an article and no products are allowed, you select from the article table. You can FK reference the article table instead of the item table, and then be sure you won't get any products in there.

You can add as many subtypes as you need for things that need to be related to in the same way. Each one gets its own subtype.

This is a common design pattern for databases that have to interact with companies and people in similar ways. They both have a taxpayer identification number (called SSN for the person). They both have birth/start dates, death/end dates. They both have addresses and phone numbers and can enter into contracts and be invoiced and pay bills and get awards, whatever.

So they make a supertype Party table. Things that can be made common between the types go into this table. Many designers choose to use RestOfName and FirstName. For companies, the first name is left blank. TIN/SSN is in this table.

When one needs to do anything that can be a person or a company, the selection or FK is made using the Party table/PartyID. If only a person is allowed, it uses an FK to the Person table.

It is a matter of personal preference whether you label the column in the person table PersonID or PartyID. I lean toward PartyID because of the confusion that can arise not knowing where PersonID originally comes from. I find the potential confusion about PartyID-FK to the person table vs. the party table less problematic.

That is, I find it better to make it clear by the use of "PartyID" in the Person table that this value does not originate here, rather than for someone later to come to the table and not even realize that the column is in fact a foreign key as well as a primary key. Naming it PersonID, while making it easier in FK tables that reference it to instantly know that one is restricting this column to only people, hides that there is anything else to know. Better to have a hint that there is something one must look up (what table does this FK to?) than fail to give notice that there is anything to look up at all and REALLY confuse someone. Another advantage is that if it comes time to allow more subtypes, the column name is already appropriate (like in your Comments table if you had called it ArticleID then wanted to expand it to allow Products as well, now it's problematic to change it to ItemID).

 

[r937]

Use the supertype/subtype design pattern.

very elegant


dunno why i didn't remember that

;-)

r937.com | rudy.ca

 

[ESquared]

Rudy,

Thanks!

I just have this pattern keep cropping up as necessary all over the place, again and again... I can't get away from it!

 

[jgd1234567]

Hi cheers ESquared, that is perfect. I also agree with your assessment of calling the fk field ItemID even though it is effectively acting as a primary key for the sub type.