Begginer in SQL-Foreign KEy to Mulitple Tables

Hey everyone,
I am beggining in SQL and the .NET framework and have been running into some problems trying to design a relational database. I am completely new to it so I bought a book that was recommended in this Forum called "Handbook of Relational Database Design" and it has been pretty usefull so far. RIght now I am trying to make the Logical Data Model before I make the Relational Data Model.
The problem that I am having right now is creating a table that is a derivation from another table. For example, in the book they have a table called Property, and then two other tables called MountainProperty and BeachProperty. MountainProperty and BeachProperty are a type (relationship here) of a property. So basically Property will hold some data in a table, and then MountainProperty and BeachProperty will extend that property to contain more specific data. This is very similar to what I want to do. However I am having a problem understanding how an instance (or row) in Property, will have a link (foreign key) to a piece of data that is in Mountain or BeachProperty. I understand the foreign key in Mountain and BeachProperty and how they will link back to their "parent". But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me.
Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row. How can I link to that data from that row in the Table Property.
I am terribly sorry if this is confusing or if it is so appartently easy for you, but this is the first time that I have ever tried to make a relational database and I am really struggling on seeing how to organize these tables properly. Thank yor for your time.
Jeremy
Hello,

Let's say that we have the following tables:
property (property_id int primary key, property_type int, some general fields)
mountain_property (mproperty_id int primary key, property_id int references property(property_id), some specific fields)
beach_property (bproperty_id int primary key, property_id int references property(property_id), some specific fields)

property_type defines the mountain (1) or beach (2) property.

So when we need to select all records for mountain property we can execute the following SQL:

selecy * from property p inner joun mountain_property mp on p.property_id = mp.property_id where property_type=1.

So genenerally, property table knows nothing about mountain_property and beach_property and here is no relationships from property to any of these tables.

This example is similar to defining a class with collections and this type of relationships is called one-to-many (for each of pair property-mountain_property and property-beach_property).

The other possible (but different) solution is to use one-to-one relationship. In that case mountain_property's primary key is a foreign key to property table. E.g. the structure is:
property (property_id int primary key, property_type int, some general fields)
mountain_property (property_id int primary key references property(property_id), some specific fields)
and
selecy * from property p inner joun mountain_property mp on p.property_id = mp.property_id
will return a list of "mountain_property" objects with all fields.

Sincerely,
Alex|||

There are a couple of examples of what you're after in the Microsoft sample AdventureWorks database.

For example the Store and Individual tables both inherit from the Customer table. Store and Individual both have a primary key of CustomerId, which is a foreign key to the Customer table. Customer has a CustomerType column, which contains either 'S' or 'I' depending on whether the Customer row refers to a Store or an Individual.

This is also the way that my Foundation product implements inheritance; it looks for relationships like this and creates business / data access objects with the same inheritance structure. You can download either the free/trial version (free for up to 20 tables) or just the Knowledge Base from my site, the Knowledge base has a helpful page in it about exactly this stuff, it's called "Defining Inheritance Relationships".

Linq-to-Sql implements inheritance in a different way. You don't have a base table and derived tables. Everything goes into a single table, and any columns that would normally go into a derived table must be made nullable. So you end up with loads of nullable columns, only a subset of which can legitimately be populated for any particular row. Of course having to make the columns nullable means a lack of data-integrity, and you can't have unique indexes that use those columns, and no one-to-one relationships including those columns. Made me shudder when I read about it! Maybe I'm just too sensitive

Linq-to-Sql still uses the "CustomerType column" way of telling what derived type a row is.

Sean

|||Thank your for giving such a detailed example, it really helped. I guess I understand here that there wont be a direct link for the Property table to find one of its children, but that it can find them because it holds the property_type id so it will know what table to search for them in.
They question I have is that in the 1'st SQL statement that you gave it says "selecy * from property p inner joun mountain_property mp" which shows me that this statement already knows that it is seaching in the mountain_property table.
But what would you do if you didn't know which table to search in. As in, lets say you have your property instance (row) in the property table, and you now want to get more information about it(which is either stored in the mountain_property or beach_property table). I think I can see myself doing this in two statements by first getting the property_type and then processing that data in my application to choose what table to choose in and then executing another statement to get the data. Is this the way that you are supposed to do it. Eg below
So I guess if I was in the Property table and I had selected row which was of type mountain_property. To get the additional information of this property (held in the moutain_property table) I would first select the property_type id. I would then use an if/else statement in my application to find out wich table to search for. eg. If(type == 1) table = Mountain; else if(type == 2) table = Beach;. Then I would make another SQL statement that would then search for the data in the table name that I got from the if/else statement.
Is this right, or am I far off from course?