3

1) Can a table have a one-to-one relationship with a number of tables !?

To clarify more, if I want to do an insert the first table will be affected and only one of the other tables will be affected .

2) and if so, how the primary key will be ?

3) also, what will the query look like if I want to retrieve a number of records from these tables ?

Thank you .

Roobah
  • 311
  • 1
  • 8
  • 16

2 Answers2

6

Can a table have a one-to-one relationship with a number of tables !?

Yes if you really mean 1:0/1:

Create Table Parent
    (
    Id ... not null Primary Key
    , ...
    )

Create Table Child1
    (
    Id ... not null Primary Key
    , Foreign Key ( Id ) References Parent ( Id )
    ...
    )

Create Table Child2
    (
    Id ... not null Primary Key
    , Foreign Key ( Id ) References Parent ( Id )
    ...
    )

This setup would require entering a value into the Parent table first and then the child tables (in no particular order) afterwards. Further, you could add a value to one of the Child tables and not the other since they both only rely on the existence of a value in the Parent table and not each other.

Addition

To select from your child tables, it would involve the same process as any other parent-child relationship. For example:

Select P.Col1, P.Col2...
    , Child1.Col1, Child1.Col2...
From Parent
    Inner Join Child1
        On Child1.FKCol = Parent.PKCol

By using an Inner Join here, i'm only return Parent rows where there exists a Child row. If you wanted all Parent rows and only those Child rows where there is a match, you would use a Left Join instead of an Inner Join. If you wanted to select data from more than one child table at the same time, you can simply include those in the From clause:

Select P.Col1, P.Col2...
    , Child1.Col1, Child1.Col2...
    , Child2.Col1, Child2.Col2...
    , Child3.Col1, Child3.Col2...
From Parent
    Left Join Child1
        On Child1.FKCol = Parent.PKCol
    Left Join Child2
        On Child2.FKCol = Parent.PKCol
    Left Join Child3
        On Child2.FKCol = Parent.PKCol
Thomas
  • 61,164
  • 11
  • 91
  • 136
  • Exactly. But my question is that insertion will be: 1) insert in parent. 2) select ID (auto generated) from parent. 3) insert in one of children. Is this right ? – Roobah Apr 21 '11 at 23:25
  • @HTB - Correct assuming you are using AutoNumber or Identity values as your primary key. – Thomas Apr 21 '11 at 23:26
  • OK. What about retrieving some records from different children. Do I need a join or I do it like this: "select * from parent, child1, child2.. where parent.id = child1.id || parent.id = child2.id.." !? – Roobah Apr 21 '11 at 23:32
  • @HTB - I've expanded my answer to show you samples of retrieving data from child tables. – Thomas Apr 22 '11 at 00:23
1

In SQL Server you could certainly design a database that is capable of representing this relationship. You could enforce the one to one relationship by having the child tables use the ParentId as their primary key and force uniqueness.

If you wanted to query a parent table and it's three children that may or may not have existing records your query would look something like this:

SELECT * FROM ParentTable as pt
LEFT JOIN ChildTable1 as ct1
ON pt.id = ct1.ParentId
LEFT JOIN ChildTable2 as ct2
ON pt.id = ct2.ParentId
LEFT JOIN ChildTable3 as ct3
ON pt.ID = ct3.ParentId

My question would be why would you break a one to one relationship into multiple tables? You could also enforce a one to one relationship with the data if you kept it all in one table. This would make for cleaner queries (no joins) and better performance.

Abe Miessler
  • 75,910
  • 89
  • 276
  • 451
  • Great. My parent table is Place. and the children are Restaurant and Hotel.. . Children have shared columns placed in the parent table (like name and tel). and each child has special columns like dish for Restaurant and stars for Hotel. So, I had to break it. If you have a better approach, please suggest. – Roobah Apr 21 '11 at 23:21
  • 1
    Ahh, I see. You are dealing with trying model inheritance in a database. I would recommend looking at this SO post: http://stackoverflow.com/questions/190296/how-do-you-effectively-model-inheritance-in-a-database. It goes over some of the various techniques for doing this. – Abe Miessler Apr 22 '11 at 15:04
  • ooh thanks a lot Abe . The link helped me a lot . Great concept . Thanks . – Roobah Apr 23 '11 at 06:29