database design help

[angela4eva]

I am creating an employee database...

this will be used by two different companies
the first table is employee and second table is departments
is it better to create a autonumber field as primary key and link it departments table or use the esixting employeeidid as primary key?
if i use the existing employeeid's as primary key then what will happen if the hr people enter wrong employee id and need to correct it...If any one has worked/seen a commercial payroll datbase design can you suggest what is the best option and best way to do this.Any help will be apprecied ..thanks you guys

 

[LNBruno]

Personally, I don't like autonumbers as a primary key. There should be a natural unique element for whatever entity you are coding for. In the case of employees, I would think there would be an employee ID number or at least a tax id number (SSN, TIN here in the states). Departments, on the other hand...

As for the relationship between employee and department tables, I would think the employee table should have a column for the foreign key to the department table.

< M!ke >
[small]Anything is possible if you don't know what you're talking about.[/small]

 

[gmmastros]

Mike,

Sorry, but I have to disagree with you. I don't think you are right, and I don't think you are wrong. Primarily, it's a different set of experiences.

I like having identity (autonumber) columns for my primary keys. While it's true that you can usually find a natural key to use, I never do. I have seen it happen too many times where a customer of mine wants to change the id number (that they think) is the unique identifier.

The real problem with natural keys occurs when you start linking one table to other tables (foreign keys). Then, when you want to change the key, you have a terrible time doing it.

Anyway, my suggestion would be to use an identity (autonumber) column for the unique identifier, and then allow for an Employee Number column. If the users ever want to change the employee number, it will be super easy to do it. Just update 1 column in 1 table for 1 row.

For data integrity reasons, I would encourage you to put a unique constraint on the Employee Number column.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

Then, when you want to change the key, you have a terrible time doing it.

sorry, george, but i have to disagree with you, it is not a "terrible time" (okay maybe you've expereinces this, but the rest of us didn't)




a natural key, by definition, identifies unique rows

a natural key is not guaranteed to remain unchanged forever -- people do sometimes change their SSNs and/or names, products do sometimes change their product codes, books do sometimes change their ISBNs, vehicles do sometimes change their VINs -- but it is rare

and it is also why ON UPDATE CASCADE was invented

please do keep on assigning surrogate keys left and right if it makes you happy -- they're okay too, but so are natural keys



r937.com | rudy.ca

 

[ESquared]

1. TIN/SSN: not everyone has one. It's also sensitive information.

2. Is there any performance consideration joining on columns of few bytes (such as a 4-byte int or say a char(4)) or on columns of many bytes (such as a varchar(100))?

3. Is there any difference one way or the other in cases where the primary key is not the clustered index?


[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[ESquared]

What is the natural unique element for a computer?
Hostname? No... in a system where modeling the complexity of clustered computers is not required, a "computer" can have many hostname: node names, clustered name, sql network name, group network name...
Serial number? So you can't enter any of the computer's information into the system unless you know this value.

How about someone that you've decided to hire and he's bringing in his SSN tomorrow. In the meantime, you can't enter anything about him into your database. Oops.

 

[Crowley16]

if there is ANY chance that the natural key is to be changed or worked with by users then use a generated key, even with cascade updates or deletes on.

If you get a system with 100s of tables and not all tables have the relationships set or cascates turned on, then changing a pk could ruin your data. PK's should NEVER change and get into that habit.

also, a generated id tends to make the most effecient joins, being a numeric whole number and usually will be the smallest necessary. This doesn't have to be clustered, just a standard index on the generated id will be fine.

there are instances where I wouldn't use surrogate keys, but these are *generally* static data or very small lookup tables.

--------------------
Procrastinate Now!

 

[ESquared]

Ints are efficient because they are 4 bytes. It doesn't make a difference if you start at 2147483647 or -2147483648. char(4) is also as efficient (or almost as efficient if collation matters which I don't know). If you can use a smaller data type such as smallint or tinyint, there can be an improvement in number of rows per page. (But I would have to research whether byte alignment played a role in the matter).