Primary Key

Hi.. I'm going to build database of university, but I have problem with primaru key, This is the situation:there are many faculities and each one has many departments,each department has many courses,each course has many sections.. The problem:I want to make those fields in the same table and make the primary key generate from other fields,(i.e) I want the faculity be integer from 4 digit "Example the first faculity start with 1000 the second 2000 and so on" and the the department of each faculity will generate its value from the faculity number+interger number from 3digit "Example the department of the first faculity start with 1100 and the second on will be 1200 and so on " the same thing will repeate for courses and sections so the sectionsID will be the primary key. Do you know hoew this idea can be implement by SQL server 2005?Please help me as soon as possible.

You can create a function in SQL Server 2005 and set the identity field to use that function. However, I would not recommend that you have a formula to calculate the primary key, this will limit your ability to grow.

You can create unique identifiers in the database tables, and through relationships determine whom belongs to what.

|||

manoool:

Please help me as soon as possible.

No. I willNOT help you do this. It is a terrible design idea. You should toss this idea on the scrap heap and build a proper relational data model.

Relational database technology was designed to provide quality engineered ways to build applications that avoided precisely the problems you are about to introduce with that design.

What happens if you get eleven departments in a "faculity" (which I assume is "facility", not "faculty"). The key value would be the same as the key value for the 2nd department in the next facility!

|||

David (the other David, not me) has given you good advice -- your initial idea is a bad one and it will only get you into trouble. You should normalize your datasomething like this -- assuming each table has an identity column as PK

Faculty Table

Department Table

Course Table

Section Table

Each Faculty member is a member of one Department (I guess they could be a member of > 1, but that's a complication I'll ignore), so Faculty Table has a FK (Foreign Key) to Department Table

Courses are "owned" by Departments, so Course Table has a FK to Deparement Table

Section Table -- I guess you mean the different instances of the same course, eg, Computer Science 101 has 3 instances. This would mean that Sectino Table has a FK to Course Table.

...or something like that...

It may seem harder to you to do it this way but I can assure you that in the end you will be very glad you did it this way. After a while you'll begin to think this way and will be amazed that you ever considered doing it differently.

|||

Thank you so much for your advices

To Mr."david wendelken " sorry for the wrong dictation

and to Mr."David " (the other one)

i was design the tables like this

but when my doctor saw it she told me that this i very bad design

because in the end I have a table for the Faculty that just contain the faculty ID and the faculty name

so, she asked me to change it

and she suggest this way that i mention in the beginning

but I don't know how to achieve it???

|||

manoool:

...
but when my doctor saw it she told me that this i very bad design

because in the end I have a table for the Faculty that just contain the faculty ID and the faculty name

so, she asked me to change it

and she suggest this way that i mention in the beginning

What is she a doctor of? Surely not a PhD in Computer Science! Is she your customer or your teacher?

The numbering scheme you mentioned will fail as soon as there are more than 10 things belonging to something.

That's bad design. You could extend the key another digit, and move the failure point to 100 things per parent item.

If you have abunch of things that are very similar in table design (i.e. id and name), it can make sense to put them in the same table.

But use additional columns to code what "type" of thing they are and what object they refer to. Three or four similar things aren't typically worth doing that for.

|||

This has a bunch of database designs already done, some of which are about schools:

http://www.databaseanswers.org/data_models/index.htm

|||

manoool:

end I have a table for the Faculty that just contain the faculty ID and the faculty name

So? That is exactly what you do need. You need to get your tables into what's known as third normal form -- seehttp://en.wikipedia.org/wiki/Third_normal_form -- this will save you from enourmous grief in the future.

|||

Thank you all for your advices

finally.....

I put one table contain the faculty name,department ID (identity) and department name

and another table contain the courses information and department ID

Of course, with existing of the other tables such as the section,student......

thank you again

|||

My Doctor: yes she has PhD in Computer Science!

Is she your customer or your teacher?

she is my supervisor in the graduating project

|||

manoool:

I put one table contain the faculty name,department ID (identity) and department name

So what happens if a faculty member changes departments? You have to not only change the department ID but also the department name. If department name is only kept in the department table you won't have this problem. It is exactly this issue that normalization was designed to handle.

|||

I don't know if I understand you will.......

let me see

if the faculty member (teachers) changes his /here departments then

I have to change just the department ID which is forien key in the teachers table

and i don't have to change the department name

any way the department ID is an identity number

just to connect this table to the other tables

not else

is this what you mean?........

|||

When you wrote,

manoool:

I put one table contain the faculty name,department ID (identity) and department name

I took that to mean that both department ID & name would be in the same table, the faculty table. If that's not the case then you don't have a problem, if it is the case then you have the problem I described.

|||

thank you for your recommendations

I really did what you said

but I don't recognize the problem

because as I described what it will happen if the faculty member change it is departments

thank you again