Hello All!

I'm very new to DB programming and want to do things correctly. So, I've decided to study the correct way to build a relational database.

I've studied some stuff on line so I understand the concept of and reason for nomalized forms 1, 2 and 3.

But I'm having a problem understanding how foreign keys work in child tables - wether they dynamically link data to/from parent tables, or if they're just static indexes to those tables.

I'm eager to study this whole process, but I'm on a very thin dime in the Philippines (I'm an American expat) and would like to request from anyone a link to a good site which would explain the functionality of foreign keys IN A VERY SIMPLE AND UNDERSTANDABLE WAY for a neophyte such as my self.

Would greatly appreciate any and all guidance on this topic.

Thanks to all in advance!

Expatriate

foreign keys in a nutshell

- parent table contains primary key
- child table contains foreign key
- the "link" is by value
- dbms ensures that every value inserted into foreign key already exists in primary key
- dbms also ensures primary key updates or deletes are handled according to specified rules (restrict, set null, and cascade are common options)

that's pretty much all there is to it

any questions?

I understand what is suppose to happen (as you've outlined) conceptually, but I don't understand HOW it works.

Here's a brief overview of what I'm doing:

I've got a "Room Categories" table. In it, there are 3 fields:

1) "categoryID" tinyint, autoincrement (PK)
2) "roomCategory" varchar (15) - a room category would be "standard, deluxe, family, or penthouse"
3) "roomDescription" varchar (255)

Note: this table will only contain 4 rows; each row will have one of 4 possible "categoryID" values: 1, 2, 3 or 4.

Then, I've got a "Room Location" table which holds room numbers and room locations (i.e. 1, 2, or 3 [floor]).

This table has 4 fields:

1) "locationID" tinyint, autoincrement (PK)
2) "roomNumber" varchar (2) -- room numbers are alpha/numeric
3) "roomLocation" tinyint
4) "categoryID_FK" (foreign key from the Room Categories table).

Note: this table will contain 17 rows (maybe more if the hotel adds rooms) but as of now, there are only 17 different rooms (in 4 specific room categories).

>>dbms ensures that every value inserted into foreign key already exists in primary key<<

That's my core question: How does the "categoryID_FK" field "know" what "categoryID" values belong to which room numbers and room locations?

The only way I can think this can happen is if I manually input the correct categoryID values into the proper rows of the categoryID_FK field (room location table).

That doesn't sound right...or is it? Have I missed something?

REALLY appreciate your guidance and willingness to help.

Expatriate

that's exactly correct, sir

look at it this way: suppose you have everything all set up properly, with primary/foreign keys, and all the rows that are in there now are referenced properly, so that each room is correctly associated via the foreign key to the proper category

okay, now here's the "Gedankenexperiment" (as A.Einstein used to call them)

suppose you now want to add a new room to the database

how is the database supposed to know which category it belongs to

you gotta tell it!



where the relational integrity part comes into play is if you told the database "this room belongs to category 5" -- the database would tell you "no way, i'm not adding that room because there is no category 5"

make sense now?

r937 -

Got it. The whisps of confusion are now gone!

Thanks so much for your help; now I can get on with the job instead of losing sleep over "what to do"!.

ONE LEAD ON QUESTION:

In my scenario, I'm trying to do this to normalized form 1, but the Room Location table WILL have multiple 1's, 2's, or 3's in the room location field as, of course, there will be multiple room categories that share the same floor.

So...where does one draw the line when it comes to trying NOT to duplicate data in a table?

Expatriate

multiple room categories that share the same floor?

no

multiple rooms that share the same category, multiple rooms that share the same floor, but not multiple room categories that share the same floor

room location (floor) is actually a foreign key, isn't it

however, if you use the value 1 to represent floor 1, and the value 2 to represent floor 2, etc. then you don't really need a room location (floor) table, the foreign key is actually a natural key but it's a "virtual" foreign key at the same time

get it?

r937 -

Sorry for the long delay in my final response. You've been a great help to me in understanding foreign keys - "natural keys" and "virtual foreign keys" have got my head swimming though...but that's okay: one step at a time!

Appreciate all the time you've taken to assist me.

I've also taken a look at your website and will be using it for reference as well.

Thanks again!

Expatriate

you're welcome