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.
The only way that I can think of to link to that table is by making another column called PropertyType where MountainProperty equal 1 and BeachProperty equals 2. So I would perfrom a query in my application on the row like "Select PropertyType from Property" which would give me back 1 or 2. Based upon that I would then know which table to search for in the database and would then perfrom another query on the specific table, either MountainProperty or BeachProperty.
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
(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.)
Hi,
I tried the answer your question but could not find it, tables in the relational model since I have been doing data back in 1998 are entities, the only property per ANSI SQL definition in the DDL(data definition language) is IDENTITY. So you design tables by files and association and relationship is determined by upper and lower bound cardinality.
There is a better book called Data Modeling by G. Lawrence Sanders and there are existing free data models you can clone to use. You questions about the relationship is through DRI(declarative referential integrity) which means if a references b b must exist so primary key a becomes foreign key b so you can use a to delete b Cascade Delete and a to update b Cascade Update and a to set b to default Cascade Set Default and Null Cascade Set Null. If the above is confusing it is because it was created with a 26 pages long algebra and took many years to implement. If you have many references then you need a DRI trigger because the built in version will not go beyond three. Hope this helps.
http://www.databaseanswers.org/data_models/index.htm
http://www.databaseanswers.com/modelling_books.htm
|||Truthfully, I didn't really understand anything you said, maybe just a little when you talked about the Cascade Delete stuff. But it was all inference. Anyone else have any suggestions for my predicament?
|||
EDIT
You did not understand it because I did not say what you expect, what I am saying the only properties relating to tables is IDENTITY so all you have been trying to create is not working because it is not correct. And spend time with the links posted because that material is by Data Modeling experts.
|||
OK, as far as I understood you are talking about realtional inheritance. This is not automatically possible in SQL Server, you will (according to your sample) have to implement that on your own. Let me explain how I see your problem: You have two tables, one tables with the common values for properties (the parent table) and the specific table according to the type mentioned in the property table. If you query the table property you want SQL Server to pick either the child values from the one or the other child table, depending on the property type in the parent table. Unfortunately and as aletady mentioned, this is not implemented in SQL Server. Although a FK / PK raltionships can be implemented you will have to you aour own retrieval logic to get the data back. You can implement this usingeither client logic or server logic (like stored procedures). Hope that hits your question, if not do not hesitate coming back.
Jens K. Suessmeyer
http://www.sqlserver2005.de
Farhan H Soomro wrote:
