Hi Mates,

I am new to Databse Design and trying to move from spreadsheets to Relational DB. For that purpose I am reading books that are for beginners. The problem is that none of the books have seperate chapters on relationships. They just mention them in passing. On the net also the relationships are not explained properly. I mean, come on, is it not all realy about relationships. I understand the fact that Relational Database and Database Relations are different. The fact that we can represent Data relationaly has nothing to do with the Relational Model (Codd). However to me they seem very important. Different tables for different Entities. Sure. But they are connected through relationships. None of the material I have read explains this important aspect clearly. And than there is Closure. But that is a different toipc so I will not burden you with that for now. I have posted now my question on 5 web sites that claim to be Database Forums. No answers.

One-to-One.



In these examples, the relationships are one-to-one. Fine. Logical. But on what ground we decide which Primary Key becomes a Foreign Key in which table. PK President ID in PRESIDENTS table becomes FK President ID in the COUNTRIES table. While the reverse may be quite possible. Which is the Primiary Table and which is the Child Table?

Further more in any table that holds the FK, I can not add any new record unless it has a record in the corresponding table. For example I can not add any new country in the COUNTRY table, unless it has a PRESIDENT. One could argue that since no country can exist with out a President, this model is logical. But one can also argue that what if I want to add a new Country that has a relationship with another Entity and the PK Country ID from the COUNTRY table is FK Country ID in that new table. For example in the case of Rooms & Employees the PK Room ID in the ROOMS table becomes a FK Room ID in the EMPLOYEES Table. Now I can not add any new Employee unless he has a ROOM. If I can not add a new Employee, I can not add a new Order.



However if I now reverse it, I can add new order. But than I can not add new Room



Any Body

in the real world its fairly unusual to have one to one relationships.. often its done for performance or security issues, or if say the db cannot handle the number of columns. There are others (better) but for the life of me I can't remember any right now.

int he real world you would have as many relationships as you felt were needed to support the real world you are modelling. its unusual to have more than 1 realtionship between tow tables, unless there are two or more separate relationships
eg you mioght have a table which identified say the signoff on a purchase order (you may then the id of the person who raised the order, the id of the person who signed off the order, the id of the person who entered the order.... each of whihc whould be a FK to a person in say your staff table.
its legit to have the fk to refer to a column within the same table

eg you have a person ID, and a manager ID, the person ID is a PK (no nulls), the manager ID is a FK to person Id in the staff table, the manager id is nullable, null indicates that this person has no manager


have a look at rudy's (r937) site
and tony marston's site deserves a good read

the terminology can be a bit tortuous at first
a PK is something that makes this row unique
a FK is something in this table that refers to another table's value, a FK value is unique in in that other table

for what its worth in your president / country model
"a president" is a distinct entity so If have a table containing details of presidents
"a country" is a distinct entity, do Id have a countries table (probably using the ISO country code as the PK
a country can have more than one president*
A president may be president more than once
its unlikely that a president can be a president in more than one country

so to get round that I'd have what's called "an intersection table"
which associates the values from the other tables countries & presidents plus any other data which is relevant to that intersection

so I'd have the President ID, the Country ID plus things like
date became president, date stopped being president. it could include reason became president (eg election, deputy), reason stopped being president (eg couldn't stand again, lost election, got caught out comitting fraud, died, overthrown......) conceivably those reason codes could justify tables in their own right. in which case the reason code in the intersection tabel becomes the FK to the reason code in say PresidentLeavingCode, where the reason code in that table would be unique and hence the PK

*unless you are an African ex colonial country run by a Marxist trained dictator where you can only have one president

Question 1 - I though that "an intersection table" is created to resolve many-to-many relationships as they are really two 1-to-many relationships.

Question 2 - Are you saying that FK values can be null.

Question 3 - But how to decide which PK goes in which Table. You can have have


EMPLOYEES
Employee ID - PK
Employee Name
Room ID - FK from the ROOMS Table.

OR

ROOMS
Room ID - PK
Room Size
Employee ID - FK from the EMPLOYEES Table.

Additional attributes as not a problem here. You can have as many as you want. For example EMPLOYEES Table can have Date of Birth, FIrst Name, Last Name and similarlily so ROOMS Table. That is not the point and does not have any problems. The Problem is

PRESIDENTS
President ID - PK
President Name
Country ID - FK from COUNTRIES Table

OR

COUNTRIES
Country ID - PK
Country Name
President ID - FK from PRESIDENTS Table.

which one?

healdem, you do not need a many-to-many table between presidents and countries

there has never been any person who has been president of more than one country (you could look it up)

JAA, the one-to-one relationship is real easy

of the two entities, one of them will either --
1. be optional
2. potentially have multiples

in your example, a president cannot be a president unless he or she is a president of a country

on the other hand a country can exist without a president (although typically it does not do so for very long)

so the FK goes into the dependent entity, the president

simple, yes?

r937,

Can the FK value be NULL?

Ok it works for PRESIDENTS & COUNTRIES. But does not work for ROOMS & EMPLOYEES. Room can exist with our Employee and Employee can exist with out Room

depends how you look at it.. the president is the head of state, there are several countries with the same head of state. even if you don't share the same head of state, I still think you need an intersection table to handle when a president held office.

..which is fine if all you want to do is register who is the current president. you cannot legislate for the eventuality that a president may be president for more than one stint, and those stints are not neccesarily contiguous

dude, we both posted at 9:37

how freaky is that!!!

its quite possible for the FK to be NULL, meaning that there is no value in the column which happens to be the FK. to use the presidents analogy it could be because there is no president. its possible however to define that column as saying there must be a value.. thats down to the detail of the design or real world model.

okay, healdem, you win

now, go back and pretend you're going to use a one-to-one relationship between president and country

where does the FK go?

Its not like Wordscraper.. there doesn't HAVE to be a winner

if you have a one to one relationship then I don't think it matters, if it had to be a one to one relationship then Id say the country AND the president should be in the same table..... I can see no need for a separate table identifying the president in that event.

it matters to the original poster here, because that's the question he's been asking


well, if the president had forty-nine columns of data, and the country had seventy-six columns of data, would you still whack them into the same table?

because when you later need to update your country for a new president, you gots a lot of splainin to do, lucy

I have never seen having a "intersestion table" between two entities that have one-to-one relationships. That will cause a many-to-many relationships to occur.

There are many reasons that the attribute is moved to become a new entity rather than remain in the same table. Two you pointed out yourself. One is Security/Privicy reasons and second is Database Engine limitations. Third may be as

PRESIDENTS
President ID - PK
President Name
President Date of Birth
Country ID - FK

COUNTRIES
Country ID - PK
Country Name
Country Population
Country Category ID - FK

COUNTRY CATEGORIES
Country Category ID - PK
Country Category Name (e.g. Terrorist, Allay, Neutral)

Shoul I have all of these in One Table.

can a country be in more than one category?

also, if all you have is category name, you should use that as your PK and FK instead of the horrible "ID" concept

Ok three things I learned but not understood.

1 - FK can be Null. If it is Null or not can be set up by Referencial Intergrity. But It can be Null.

But that Just leaves an ORPHAN Child Record???

http://www.r937.com/Relational.html

2 - Here we have a case where Two Table Share the Same PK. Can two Tables have the same PK?

3 - In a 1-to-many relationships it is obvious where the FK goes. To the many Side for we can not have vice versa as it will cause data redundancy. But it is not clear in 1-to-1 relationships.

talking purely in terms of the president / country example
if you don't care about the presidents over the years then I see no point in distinguishing to two entities.

if the table containing thr presidents was say a table containing prominent people or politicians then I'd expect a FK in the that table to indicate what country they are "prominent" in
I think its reasonable in that event to have the president of a country as a FK in the countries table

if the president had 49 columns of data then thats a good reason to have a one to one relationship, the FK would be country in presidents to the pk of country.

but as said before my preferred implementation would be a table for presidents a table for countries a table which resolves which president was president in which country for what period.. that caters for knowing who was president over time.

if all you care about is who is the president of country X then I still don't see the need for a separate table, the president has become an attribute of the country, rather than an entity in their own right.

I appreciate that there are no presidents who have been president in more than once country, but we don't know if that is a valid constraint. I don't know the details but I wouldn't be surprised if that did hold in the dissolution of Jugoslavia, it could well happen in the dissolution of the UK.

looking at the next example the OP talks about
which talks of employees and rooms, again I woudl go down the route of an intersection table, a one to one doesn't cut it

a room may have none, one or more employees in it
an employee may work in more than one room

so I would look to
a table for employees
a table for buildings
a table for rooms (fk in building)
a table for employees in rooms (with a fk to employees AND rooms)