Results 1 to 6 of 6

Thread: Relations

  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Relations

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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);

  3. #3
    Join Date
    Dec 2009
    Posts
    3
    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...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  5. #5
    Join Date
    Dec 2009
    Posts
    3
    Thank you but what do you mean saying "comment_id references comments" (e.g in images table)?
    Could you post an example?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    "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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •