SQL Server : Multiple relationship for the same field

dilkonika

Hello ! I have this situation : Table : Article Id name category ......................... Table : Services id name nr value ------------------------- Table : Sell_item Id item_id quantity price value ---------------- Now the problem is this : The field Item_id on the sell_item table can be the id for an Article or a Service. So I need to create a double relationship for this field one with Article table an one with Service table. Is this possible ? If not , or if I'm wrong how can I solve this situation ? Thank you.

Wendelius

If I understood your question correctly you would use the same field (item_id) for two separate purposes. If that's correct, in my opinion you have something wrong with the database model. Concerning your example I'd suggest something like:

Product

Id
name
category
IsService
nr
value

Sell_item

Id
product (former item_id), foreign key to Product
quantity
price
value

The product table would hold all sell able items and the fields are a combination from your both former tables. Also I wouldn't store the value (at least not as a normal column) since it can always be calculated from quantity times price.

dilkonika

ok , but as I can see you have merged my 2 tables. Me to I have thinked this , but the problem is that in my post I have included only some of the fields from 2 tables. In reality these tables have 15 and 13 fileds , and do you think is a good choice to have a table with 28 fields where for each record only 15 or 13 fields will have the data and others are just unnecessary ? Can be COALESCE a help in this situation ?

Wendelius

In my opinion it's ok to have merge the tables and have nullable columns as long as the data is suitable concerning the table purpose. However, without seeing the whole model, it's impossible to say if this is feasible solution in your case. As a rule of thumb one column stores data only for one purpose. What I mean is that even though you can do a dual reference, it's not advisable. Another approach could be to use a whole separate table for the links. Consider the following

Article

Id
Name
Category

Services

id
name
nr
value

Sell_action

sell_Id
other possible fields describing a single selling action

ArticlesSold

sell_Id
article_id
amount
price

ServicesSold

sell_Id
service_id
amount
price

Again this should be verified against the whole model and I must admit, I would put all the effort in modeling the sell able products properly instead of thinking how to simultaneously reference two separate tables. That would keep the model simple and clear :)

Mycroft Holmes

In the rare time when I have had to do this I live without the FK, it is not possible to have the FK to both child tables. I suspect your 28 field count on the merged table is not valid, there should be some cross over (description in both tables). I would also have no compunction about merging the tables and having some nullable fields as Mika suggested.

Never underestimate the power of human stupidity RAH

data modeling guy

Dilkonika, This is a typical supertype-subtype situation. I would consider ITEM as a supertype and ARTCLE and SERVICE as subtypes. Common attributes of ARTICLE and SERVICE should go to ITEM and there should be identifying relationship from ARTICLE and SERVICE to ITEM. I dont know how to attach an ER Diagram hence pasting a DDL for the schema. CREATE TABLE [ITEM] ( [item_id] char(18) NOT NULL PRIMARY KEY, [name] char(18) NULL ) go CREATE TABLE [SERVICE] ( [item_id] char(18) NOT NULL PRIMARY KEY, [nr] char(18) NULL , [value] char(18) NULL ) go CREATE TABLE [ARTICLE] ( [item_id] char(18) NOT NULL PRIMARY KEY, [category] char(18) NULL ) go CREATE TABLE [SELL_ITEM] ( [id] char(18) NOT NULL PRIMARY KEY, [quantity] char(18) NULL , [price] char(18) NULL , [value] char(18) NULL , [item_id] char(18) NULL ) go ALTER TABLE [SERVICE] ADD CONSTRAINT [R_2] FOREIGN KEY ([item_id]) REFERENCES [ITEM]([item_id]) go ALTER TABLE [SELL_ITEM] ADD CONSTRAINT [R_3] FOREIGN KEY ([item_id]) REFERENCES [ITEM]([item_id]) go ALTER TABLE [ARTICLE] ADD CONSTRAINT [R_1] FOREIGN KEY ([item_id]) REFERENCES [ITEM]([item_id]) go