I have just started getting into to SQL and am completely brand new to the whole concepts of relational databases. Someone on this forum pointed to the MSDN videos on LEARNVISUALSTUDIO.NET which have been very helpful. Unfortunately while learning about relational databases and looking at the program that I want to design and make using them, I have run into a pretty big wall, concerning the primary key and foreign key.
For my program, I am trying to save an object, and lets say the base class is SLIDE. Now SLIDE will store basically most of the information that I will ever want to store, such as timeCreated and mainText and slideID(primarykey). But there are other classes that derive from slide that will store just a bit more information than that because of what they are. Lets say there is a class derived from SLIDE called PERSON that stores its parentNode, which is to say something that this slide specifically belongs to and has a reference to. Now the tricky part is that in this program, every single slide can have a reference to another slide, that you would see displayed and that you could just click on and view if you wanted to.
Now relating what I just told about the classes in my program to a relation database standpoint is what confuses me. If I make a table SLIDE, it would hold incomplete data about the PERSON object, because that object has more data than SLIDE has. The answer to this was to make another table called PERSON, which would have more columns. But now we arrive at the big problem: The primary key called maybe SLIDEID would be different in this new PERSON table than in the other table called SLIDE (or any other derived class). Therefore the link between these tables is messed up. In my object orientated mind I am thinking of static class variables that would still stay constant for derived classes, so that when I make a PERSON slide it will increment off of the primary key of the SLIDE table. In other words, is there some sort of super TABLE that you can derive from, like an abstract class, where the primary keys of other tables can build off of because they will be used as the same type of reference to eachother.
If none of this made sense to the reader, I am greatly sorry. I do not really know what else I can say to convey to you the problem I have. Maybe its because I am so used to object orientated languages that this is making it so difficult to explain. If however you do understand what I am talking about, please think about it and help me find a solution to this problem. I am not an experienced programmer, but I do very much enjoy it and I am very excited about starting to make this program, and I have learned that before I start coding it is very important to have a very firm design in mind first.
Thank your for reading,
Jeremy
fwiw, I'll try to give you a nudge in the right direction.. =;o)
First off, you're completely right in that having a firm design (along with an understanding of the same) in mind before starting, is a very good thing.
Having said that, it's not certain that it'll be an easy or fast journey to get there.
You're currently thinking objectoriented, and trying to fit that into a relational design. Here's where the difficulties start.
Object orientation and Relational theory are quite different, to say the least...
Probably the best place to start, is to begin reading in Handbook of Relational Database Design
( http://www.amazon.com/Handbook-Relational-Database-Candace-Fleming/dp/0201114348 )
This is pretty much (still) the 'bible' on the subject.
It's not productspecific, so there's no 'specials' in there, but you'll find everything you want to know about how-to and why
and the reasonings and rules of what makes up a 'good' database design, and imo it's all pretty 'easy-reading' too, along with
examples and explanations about why different things may or may not be a good idea, and what sort of problems or benefits
different design choices may give you.
Once you get a grip about how the relational world works, you can then see how to apply that to how you want your program to work.
Additionally, if you just google on 'relational database design', you'll get a bunch of links to different online tutorials
(at least it looks that way, haven't looked at all)
Good luck, and welcome to 'our' world =;o)
/Kenneth
|||Dear Kenneth,Thank you for the reply, I'll see if I can get the book so I can get a much better idea on what relational database design should all be about.
I have been thinking about my problem a little more lately and I think that I have come up with an idea. I can use my program in C# to keep track of the identities keys(maybe an int but leaning forward to a string, where like the first three letter distinguish what type of object it is, ie. PER-4539) through the use of a class static variable and then when I create a new entry into the table I input that key directly, instead of letting the database handle it. So basically I use C# to handle this problem instead of SQL. Is that a good idea, or should I still think of something else?
Jeremy
|||
Well, as a db guy, I shiver at the thought.
There are some 'stuff' out there that does keymanagement along those lines, though.. But only because you can doesn't always mean you should.. =;o)
Something about keys...
A key is the 'identificator' within a domain. A domain in this case, is typically a table. A table is also many times referred to as an 'entity'.
There are a few different names for different flavors of keys, such as Primary key, Alternate key, Foreign key, Super key.
The purpose of the Primary key is to single out the one unique row.
The PK may consist of a single column or more (composite key).
It's called 'Primary' just because it's the one chosen 'primarily' among the available alternate keys within the table.
In general (pertaining your question) it's not a good idea to have an 'intelligent' PK.
That is, the key shouldn't be any construction with some innate meaning or code. It will bring you more trouble than joy in the long run.
The Alternate Key could serve as a PK, but isn't declared as a PK, thus being 'alternate' by name.
The Super Key is all the columns in the table. It's always there automagically, and could be used as a PK right away,
but that wouldn't be very practical in most cases. (and there's also limitations on how indexes can be created)
A Foreign Key is just what the name implies - it's a key from 'somewhere else'.
'Else' meaning another table, or entity.
(sry for the lack of a creative example) =;o)
For example, you have an entity (table) 'Parent' that has some attributes. (columns)
Among the columns you decide which one is to be the PK.
The Parent table has a relation to another table 'Child'.
The relation is zero to many. That is, a parent may have zero, one or more children.
Child is then another entity (table) with some attributes (columns) of it's own.
Child has it's own PK, but how to know which child belongs to which parent?
In the Child table, you also place the PK from Parent, which then becomes a FK in Child.
As you may notice, we're heading into unknown waters pretty quick, and most of this stuff are related to other stuff that is related to other stuff.. etc etc
As soon as we start thinking about tables, columns, keys and relations, the question also arises about which columns should go into which table?
What is the difference if the PK is a single or composite key?
Here's where 'normalization' starts to show, which is another thing to consider.
I like this site http://www.datamodel.org/reference.php for it's simplicity.
It shows a great overview of the rules for normalization and also about cardinality - both are a 'must know' for you.
If you go there and browse around a bit, I think that you'll get some more pieces into place.
You'll also find, that in this world there are very few (if any) absolutes or 'right answers'.
Practically everything is depending on scope and/or context - what is the good choice here, might be not good over there etc.
All in all - 'it depends'
/Kenneth
|||Once again Kenneth, thank you so very much. I will definintly be trying to get that book and will look through that website.Jeremy
