How to select primary keys for transaction and master tables?

Question

When design a database, for example lets say I have tables called SalaryTrans, EmployeeMaster and AttendanceTrans. Assuming its normalized up to 3rd normal form, table fields would be as follows... (only related 3 table are shown)

SalaryTrans (salary_id[pk], employee_id[fk], month, ..., net_salary )

EmployeeMaster (employee_id[pk], name, ...)

AttendanceTrans (attendance_id[pk], employee_id[fk], in_time, out_time, date)

NOTE:

salary_id and attendance_id are integer auto increment fields.

employee_id is a string like "OA123".

[pk] - Primary Key

[fk] - Foreign Key

Question:

1. Have I selected primary keys properly?
2. Should I use a integer auto increment id field for EmployeeMaster as well (making employee_id a unique but non primary key)?
3. For example in table SalaryTrans if I change primary key to [employee_id + month] (as this composite key would be unique as no employee would get salaries twice a month)
4. Any suggestions towards best practices and standards ?

Answer 1

That would depend on the purpose of the tables. SalaryTrans could have a composite key of employee_ID with month and year. Seems silly to have a Salary_ID field unless it's to alleviate developer burden. Even if two people had the same salary the fact that employeeID is on the table negates the fact that two different employees could be linked to the same record so salary_ID really will not be a FK to other tables. So a composite key seems fine. Say I've worked at company X for 10 years. Is my salary for march the same each of the 10 years? I hope not...

EmployeeMaster seems reasonable to have a PK named employee_Id. String works just as well as a number in this case just so long as it's unique and not null. I don't think adding a auto number adds anything special. a SLIGHT performance gain as integers process faster than string but unless your an Amazon, Google, or a HEAVY Load site I doubt it matters enough to care right now.

You're going to have a problem with attendance_Trans as what "date" gets stored when 10PM to 6AM shift occurs? IN addition what happens when people don't key in or out... rather long, or short periods. or what happens if someone puts in 25 hours in earnest? Would it be better to record date/time in and date/time out?

All that aside, will this work... It could. I don't see anything that couldn't scale here. There's nothing fundamentally wrong that I can see that couldn't be scaled from this design. Just a few choices I would probably make differently.

In regards to questions asked of:

Ok if I make employee_id with month/year the primary key of SalaryTrans table, do you recommend to remove the auto number field salary_id or keep it as only a unique key?

Personally I would remove it; however if you plan on using such a table frequently across multiple transactions without having a common object to reference, adding keeping a primary key with the auto number would make the development of those transactions less difficult. Personally I'm more of a fan for using existing fields; but I understand some existing tools prefer 1 key columns. If you're using such tools then keep it.

Generally would it be advantageous to have such a field on every tables? Only if the tools used for development require keys to be 1 field. It will make understanding the tables a bit easier as succession planning and others inherit the design. However, be certain that this field is not maintained or rendered by the users in anyway or rendered. The natural keys make the most sense to the user so that's what we should present them with. Surrogate keys are easier for systems to develop/maintain so that's what developers can should/use. However, to reduce technical speak and ease communications with clients, I've found sticking to Natural Keys in development makes managing requirements overtime easier; though it complicates development some. I personally would rather have my job be harder, to ease communications with my client groups.

Also kindly let me know how to select a composite primary key for AttendanceTrans table? Would employee_id + in_time_date work as same employee cant log in twice at the same time? or an ID field (auto number)would be better over this?

Employee_ID + In_Time_date would work as it would/never be duplicated.