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

Shivrain K Vinod gravatar image

Shivrain K Vinod
31 3 3 4

(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

Jay Bonk gravatar image

Jay Bonk
1.4k 2

(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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
Right there with you. A good structure has so many benefits, but convincing people seems pretty difficult.
Dec 09, 2009 at 02:37 PM Grant Fritchey ♦♦
Before go ahead please just look at this http://ask.sqlservercentral.com/questions/2314/sql-server-2005-time-span That is Employee Attendance Table. And About the vacation,Tour n Holiday I have to design Separate Table. Hope this will help out you to sort out my Problem Thanx
Dec 10, 2009 at 08:31 AM Shivrain K Vinod
Shivrain, you had a general question. You got a couple of decent answers and great comments. If you want a full blown database design, you'll need to hire, and pay for, one of us as a consultant.
Dec 10, 2009 at 09:59 AM Grant Fritchey ♦♦
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 1131 times

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