Design Database

[ychhuong]

I have a problem in designing table for a garment factory.

There are departments in the factory. In some departments, there are groups, but some there are no groups.

Please help me to design the tables for tblEmployeee, tblDepartment and tblGroup

[szlamany]

More information needed - please!

What is the DB you are using (backend)? How many rows might be in these tables?

One thing I can say from experience is to avoid IDENTITY columns - having a primary key that is a silly unique number doesn't help identify data (departments and groups). IDENTITY columns seem to be used in MS SQL SERVER by old ACCESS programmers...

Give some more details as to what might be stored in these tables...

[mendhak]

Why shouldn't he use IDENTITY columns? What would you suggest as an alternative to it? I'm guessing something like GROUP738 as values.

What about when new groups are added? Wouldn't it be more efficient to simply allow for the increment to occur instead of us having to implement logic for it?

[szlamany]

Why shouldn't he use IDENTITY columns? What would you suggest as an alternative to it? I'm guessing something like GROUP738 as values.

What about when new groups are added? Wouldn't it be more efficient to simply allow for the increment to occur instead of us having to implement logic for it?

oK - it was a bold statement

We do use identity columns for places where they provide a purpose. Like a master id in a MASTER TABLE. New person added - new id automatically assigned. Health claims in a claim table - new claim - new id - those make sense.

When it comes to COURSES in a COURSE table in a HS - they already have a value that the whole school knows. 0011 is English 11 - 9900 is Lunch - they've had these "values" for 20 years.

So in our COURSE TABLE, our PRIMARY KEY is 3 parts:

YR (int)
BLDG (int)
CRS (varchar(4))

By having a multi-part key like this, CLUSTERING the index actually has value. I've seen student database systems where they assign an IDENTITY column to every course - 1, 2, 3, 4 and so on.

When you look at that data with EXCEL, for instance, it means nothing.

So - again, I made a blanket statement - I just thought that we all might head down that "super-3rd-normal-form" path and start defining IDENTITY columns for DEPARTMENTS and GROUPS. I was just throwing up a road block to that. Without knowing what the real data is, it's nearly impossible to help this person anyway

[mendhak]

Understood, Agreed, yeah baby.

[ychhuong]

My problem has been solved.