Relations

mbertoli · #1 (**permalink**) 12-15-09, 04:21

Hello,

I have 3 tables: A, B and C.
Table A is in relation (n:1) with B and with C.
Tipically I store in A the B.Id (or the C.Id) and the table name.

e.g.

A.ParentId = 1
A.TableName = "B"
---
A.ParentId = 1
A.TableName = "C"
---
A.ParentId = 2
A.TableName = "B"
---

Is it a good solution? Are there any other solutions?

Thanks in advance

andrewst · #2 (**permalink**) 12-15-09, 06:28

I don't like it, because you can't use a foreign key constraint to enforce integrity. You could do this:

Code:

create table a (b_id references b, c_id references c,
   constraint b_or_c check
    (  (b_id is null and c_id is not null) 
    or (b_id is not null and c_id is null) 
    )
);

insert into a (b_id, c_id) values (1, null);
insert into a (b_id, c_id) values (null, 1);
insert into a (b_id, c_id) values (2, null);

mbertoli · #3 (**permalink**) 12-15-09, 06:35

Thank you for your reply, andrewst.

Unfortunatly your solution is not scalable: suppose to have n tables in relation to A, you have to add n columns to A.

I think it is better to post a concrete example: web site db schema.

Comments (A)

related to:

Images (B)
Posts (C)
Events (...)

and so on...

andrewst · #4 (**permalink**) 12-15-09, 07:02

Right, now your intent is clearer. A common solution to this is to turn the relationships around:

Code:

create table comments (comment_id integer primary key,
  comment_text varchar(...));
create table images (image_id integer primary key,
  comment_id references comments);
create table posts (post_id integer primary key,
   comment_id references comments);

Or you could use a supertype/subtype model, with the comments held in the supertype:

Code:

create table objects (object_id integer primary key, object_type varchar(10), 
  comment_text varchar(...),
  constraint obj_uk unique (object_id, object_type)
);
create table images (object_id integer primary key, object_type varchar(10),
  constraint image_object_fk foreign key(object_id, object_type)
    references objects
  constraint image_type_chk check (object_type = 'IMAGE')
);
... etc.

Note the use of a "superkey" obj_uk in the supertype to ensure via the foreign keys that each object is only of one type.

mbertoli · #5 (**permalink**) 12-15-09, 08:01

Thank you but what do you mean saying "comment_id references comments" (e.g in images table)?
Could you post an example?

andrewst · #6 (**permalink**) 12-15-09, 08:19

"coment_id references comments" is a short-hand way of declaring a column as a foreign key to the primary key of the "referenced" table. So this:

Code:

create table images (image_id integer primary key,
  comment_id references comments);

is equivalent to this:

Code:

create table images (image_id integer primary key,
  comment_id integer,
  foreign key(comment_id) references comments (comment_id)
);

Example:

Code:

insert into comments (comment_id, comment_text) 
 values (123, 'This is a picture of my cat');
insert into images (image_id, image_file, comment_id)
 values (456, 'mycat.jpg', 123);

Of course, this model only allows one comment per object, which may not be want you want.

Internet Services

Web Development

Digital Marketing

Consumer Tech