How to design Bulk database/Table

Hello Every Buddy Again.

Now i Have to Design a DataBase Table for Employee Record n Attendance

My Requirements are:-

  1. Around 5000/Shift Employee (The Number is keep growing )
  2. Three Shift in a day (5000*3)
  3. Have to Maintain data for at least for 5 year (5000 3 365 * 5)

Please tell me how should i design the table so i never have problem to maintain Data about Employee record and His Attendance Record.

more ▼

asked Dec 09, 2009 at 08:16 AM in Default

avatar image

Shivrain K Vinod
31 3 3 6

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Other than being able to say, that the basic employee information (name, address, phone, etc.) should be kept separate from the time and attendance information, you would need to provide additional information on what is required.

  • Are you just tracking that the employee showed up for the shift?
  • What about when someone leaves early?
  • What about when someone works overtime?
  • Do you need to track lunches or breaks?
  • Are other items needed to be tracked, company meetings, training sessions?
  • Do employees work in multiple departments / stations? Is that information needed?
  • Do you need to differentiate time off, vacation versus sick versus holiday?
  • Do you need to incorporate scheduling information?
more ▼

answered Dec 09, 2009 at 10:45 AM

avatar image

Jay Bonk
1.4k 2 4

(comments are locked)
10|1200 characters needed characters left

Based on what you've described here, I don't think you have a single table. From what you've described, I see at least three different entities, Employee, Shift, and EmployeeShift. With so little information it's hard to be sure, but it makes more sense to break these out for seperate storage. That way you can turn an employee on or off, modify the shifts, and do it all without affecting the historical records kept within the EmployeeShift table.

Again, that's based on inadequate information, so as you supply more details, that suggestion might change.

more ▼

answered Dec 09, 2009 at 12:37 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

And then there is the possibility of partitioning aged data to slower storage...

Dec 09, 2009 at 01:18 PM Blackhawk-17

Fully agreed. It could potentially be done in one table, but normalizing the data properly has benefits in storage space and can help guarantee consistency if done properly. As a general rule, I recommend taking data structures to the third normal form.

There are some exceptions, but they generally involve very specialized analysis or reporting structures where data changes are infrequent.

Dec 09, 2009 at 01:20 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 09, 2009 at 08:16 AM

Seen: 1402 times

Last Updated: Dec 09, 2009 at 08:16 AM

Copyright 2018 Redgate Software. Privacy Policy