Creating Relational Databases(Primary/Foreign Key?)

Hey everyone,
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

Creating foreign key if exists.

I have the following SMO code:

// Create options

ScriptingOptions so = new ScriptingOptions();

so.IncludeIfNotExists = true;

so.DriAll = true;

// For scripting check constraints

so.DriChecks = true;

// For scripting extended properties

so.ExtendedProperties = true;

// Don't allow system objects

so.AllowSystemObjects = false;

StringCollection individualForeignKeyCreation = foreignKey.Script(so);

Notice that the scripting option is set to include if the table exists. But the script that is generated is like:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ReasonCodeSubCategory_ReasonCodeCategory]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReasonCodeSubCategory]'))
ALTER TABLE [dbo].[ReasonCodeSubCategory] WITH CHECK ADD CONSTRAINT [FK_ReasonCodeSubCategory_ReasonCodeCategory] FOREIGN KEY([ReasonCodeCategoryID])
REFERENCES [ReasonCodeCategory] ([ReasonCodeCategoryID])
ALTER TABLE [dbo].[ReasonCodeSubCategory] CHECK CONSTRAINT [FK_ReasonCodeSubCategory_ReasonCodeCategory]

If "if" condition takes care of the first line but it seems that there should be a BEGIN END so the "if" condition holds for both ALTER statements. The reason this came up is when I try to run this script I get an error. I could modify the string collection but even this has been made difficult because it seems the 'if" and the first ALTER statement are one string in the collection returned from Script.

Any suggestions?

Thank you.

Kevin

Kevin,

Thank you for reporting this issue. This is a defect in SMO. I opened a bug report and we will fix it in the next realease (the fix will likely appear in one of the upcoming CTP releases)

Creating Foreign Key constraint

I have two tables; each has multi-column primary keys. I need to create a foreign key relationship between the two tables.

Sales:

DKey, int, PK

OKey,int, PK

RKey,int, PK

...

Rep:

Bkey, int, PK, identity

Rkey, int, PK

...

When I try to like Rkey from both tables as a foreign key relationship, I get an error that the columns in one table "do not match an existing primary key or UNIQUE constraint. (I've tried it both ways and get the same error.)

How can I link these two tables?

Thanks.

Make an unique index on Rkey from Rep or Sales depends on the table you can delegate "Primary Key" (use Database Diagram for usability)

|||

Since you did not include rationale for the key selection, this is going to be just a guess.

The purpose of a Primary Key is to uniquely identify a row of data. It looks like each Rep is uniquely identified with the IDENTITY field BKey. I'm not sure why you have RKey also involved in the Primary Key for Rep.

You could recreate the Primary key for Rep using only the Bkey, and then add the Bkey field to Sales to relate Sales to Rep.

OR, if RKey is unique, use it as a Primary Key.

However, if for some silly reason you HAVE to have a combination of Bkey and Rkey as the Primary key in Rep, you will have to add both columns to Sales in order to create the relationship.

As a side note, the use of Bkey, Rkey, etc. for column names is very 'odd', and not only adds confusion, but will be difficult to maintain the pattern for all tables. A 'best practice' naming convention for an IDENTITY field (such as Bkey) is to use the table name and the suffix [ID]. So a preferred name for Bkey would be RepID. The name instantly communicates where the field comes from and what it is, expecially useful when it is added to another table.

|||

I was trying to simplify the question....I probably should have included more details. I'm sorry, I never know how much detail to go into.

What I'm actually trying to do is create a many to many link between:

Sales:

DateKey, int, PK

OrganizationKey, int, PK

CustomerKey, int, PK

RepBridgeKey, int, PK

....

RepBridge:

RepBridgeKey, int, PK, identity

RepKey, int, PK

Rep:

RepKey, int, PK, identity

.....

I can link RepBridge and Rep via RepKey but not RepBridge with Sales via RepBridgeKey.

From your answer, it looks like I would have to include RepKey in my Sales table and do the FK link on both fields?

Thanks.

|||

No, I think you can only make an unique index on RepBridgeKey from RepBridge (is possible because is identity)

You can make the following links vis-a-vis your vision of table schema:

Rep <RepKey-->RepBridge

RepBridge <RepBridgeKey>Sales

|||

John,

In RepBridge, I'm still not sure why you need RepKey as part of the Primary Key. It seems like BridgeRepKey is a unique identifier and would adequately serve as the PK (and the FK in Sales).

RepBridgeKey 'should' be all you need.

However, if, as said earlier, there is a business reason to complicate the Primary key in BridgeRep by adding RepKey -then you will have to add RepKey to Sales -at which point, the RepBridge table seems unnecessary. You would have to duplicate the rows in Sales for each Rep if there are multiple Reps involved with a single Sale.

Your sales table probably needs a better defined primary key. Typically a unique sales identifier is created -something like an INVOICE number, SalesOrder number, etc. In your Sales table above, only one sale would be allowed per day per Rep. That may be reasonable, but is it realistic? I would prefer a [SalesID int IDENTITY Primary Key] field

Typically, a 'bridging' table will have an IDENTITY field as PK, and the PK's from BOTH others tables as FK.

RepBridge

RepBridgeKey, int IDENTITY PK

RepKey FK to Rep

SalesKey Fk to Sales

|||

I see what you're saying....the bridge table does not need the RepKey as part of its primary key. I was thinking that each new sales rep would have to trigger a new bridge record but this can be done with only one PK identity field.

There is a reason behind the key fields in the sales table, but perhaps I shall also revisit that side issue.

Thanks again.

Creating Fireign Keys....How?

Hi!!
How I can create foreign keys using Enterprise Manager without writing any
SQL commands'
Thanks in advance!
TimurHi,
Inside the table creation option from Enterprise manager, click the manage
relationship icon.
Thanks
Hari
MCDBA
"" <tim_@.pochtamt.ru> wrote in message
news:Oa3RUvY8DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi!!
> How I can create foreign keys using Enterprise Manager without writing any
> SQL commands'
> Thanks in advance!
> Timur
>

Creating Fireign Keys....How?

Hi!!
How I can create foreign keys using Enterprise Manager without writing any
SQL commands'
Thanks in advance!
TimurHi,
Inside the table creation option from Enterprise manager, click the manage
relationship icon.
Thanks
Hari
MCDBA
"ôÉÍÕÒ" <tim_@.pochtamt.ru> wrote in message
news:Oa3RUvY8DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi!!
> How I can create foreign keys using Enterprise Manager without writing any
> SQL commands'
> Thanks in advance!
> Timur
>