Noob Relational design question!

[Eye Doc]

I am working on a database to store data from scientific samples and I have a question regarding the best relational design.

For example - I have a 'main' sample table that stores the lab code for each sample and subsidiary tables that contain other data, for example, patient data (name, age and so on).

Should I relate everything off the lab code primary key? So that this 'main' table will virtually be a single field containing the lab code. The other tables will therefore contain primary keys that are not use for relationships.

Or should I create more fields in the main table that relate to the primary keys in the other tables? So that the sample table would have a number of fields that each relate to each of the other tables.

Apologies if this has been answered before and if it is a complete noob question!

[Genx]

There's no such thing as a noob question :

Now, its up to you, but remember that your relationships can span multiple levels. There's no real reason for your main table to contain only one field, but like i said its up to you.

I'm not exactly sure what a lab is, but in your main table, you would store data that you want to be able to access from your sub tables.

For example:

A simple contact management table database might be structured as follows:

Main Table:

ID (Primary Key)

Postal Address

Postal Suburb

Postal State

Sub Table 1 (a suburbs table related to main table by postal suburb, postal state):

ID (PrimaryKey)

Postal Suburb

Postal State

Postal Code

Sub Table 2 (names - related to main table by foreign key to primary key)

ID_ref (ForeignKey)

Title

FirstName

LastName

Sub Table 3 (phone numbers - related to main table by foreign key to primary key)

ID_ref (ForeignKey)

NumberType

Number

etc etc.

Hope this helps and good luck.

~Genx

[Eye Doc]

SO each table does not necessarily need a primary key, unless that pk is being used to relate or identify something?

[Genx]

Correct

[Eye Doc]

Many thanks!

I have been putting a pk in all tables regardless and I think that has led me astray in terms of design.

Thanks again!

[oakbridge]

I respectfully disagree with the previous poster. Technically you don't need a primary key in each table, however it is good design to have a primary key in each table.

I am working on a previously designed system where many of the existing tables don't have primary keys. I'm finding that as we need new functionality, I have to go back and add a primary key to the existing table.

In any of my database design (stretching back 15+ years) I have always included a primary key in each and every table.

Hope this helps,

[Genx]

... Why? Why would you want a primary key in a table such as phone numbers. What could you possibly ever relate to this. Same thing with names, at the very bottom point in the relationship, I don't think its absolutley necessary, and its in no way a design requirement.

As far as i'm concerned, it's a bit of a waste of space... Why put one in if you don't need it, that's the whole reason its not a compulsary thing in FM.

But on the other hand, i just had a look at my database. Of 48 tables, 35 have unique primary key identifiers, whilst 45 have foreign keys. The 3 that don't are global tables. But, the 10 that don't have primary keys, are plain and simple at the bottom of my relationships, where nothing could ever logically be related to them.

Oh well, i suppose it's up to who's making the database and how dodgey the relationships are going to be in the end.

Just an opinion.

~Genx

[LaRetta]

Why? Why would you want a primary key in a table such as phone numbers. What could you possibly ever relate to this.

Well for starters, a Person table can share the same phone number. Phone numbers can also relate to a Departments table and again ... one-to-many. And there are even many-to-many examples here and I agree with Steve on this one.

Genx, you aren't looking deep enough ...

Besides, the length of time spent considering a serial might be better spent just implementing one. They use little resources and MOST are related somewhere.

LaRetta

[Genx]

Point taken, input into thought box!

I can see your point of reasoning, however it depends where your coming from. I.e. if your database stores contact information for 10000 properties within an area, phone numbers are not likely to be cross relational. If you have contact reminders, notes, diary appointments etc. they to are more likely to be tacked on to specific records, that is literally a one to many relationship vs. a one to many to one relationship. (now im just confusing myself)

But yes, i suppose they don't use to many resources and it's definitley quicker to set them up and have them if they're required than to turn back later and realize that they might just have been useful.

So as mentioned before, point taken, and you guys have probably experienced the problem in your long dev careers more than i have in my short 6 months or whatever its been.

I do enjoy these forums so much.

But yes, back to the point, um, yeh, i'll mull it over, but will probably start implementing them just incase (so as not to have any regrets).

Cheers,

~Genx

[Genx]

I almost stabbed myself in the foot by using an example of a company database. But then again, in the end it would likely consist of:

Main Company (one to many states)

//might have relationship to phone to list multiple phone numbers for main company

- State Division (one to many stores)

//might have relationship to phone to list multiple phone numbers per state

- Store (one to many departments)

//might have relationship to phone to list multiple phone numbers per store

- Department (one to many employees)

//might have relationship to phone ot list multiple phone numbers per department

- Employee (one to many phone numbers / emails etc)

End of relationship --- Phone Number

How would those phone numbers at the end that relate directly to the employee be relatable to another employee via a primary key in the phone table, same with the rest?.. This is a real question because i'm actually dumfounded (is that how you spell it?) at the moment.

Hmmm..