Database design. Please Help

[Dawie]

My problem is my MySQL database/table design. Let\'s assume the following:

 

1. The database must be designed for 65 000 employees.

2. Each employee has roughly about 1000 fields (columns).

3. 900 of the 1000 fields are of type \"float(16,2)\" (Salary, Tax deduction etc)

4. 50 of the 1000 fields are of type \"date\" (Date of Birth, Date Joined etc)

5. 50 of the 1000 fields are of type \"char\" (Surname, Inits, Address etc)

 

I now sit with the following design options:

A) One massive table 65 000 rows and 1000 columns where employee number is the unique primary key

OR

 

B) 3 tables with 65 000 000 rows and and 4 columns each

col1 = auto-increment which is the unique primary key,

col2 = employee number, (in other words about 1000 per employee)

col3 = value description, (a field to identify what col4 is eg.\"Date of Birth\"),

col4 = actual value (amount or date or char)...

OR

 

C) splitting the massive table into say 3 tables:

- table 1: (dates) 65 000 rows, 51 cols where col 1 is employee number (unique, primary) and the remaining 50 the actual dates

- table 2: (text) 65 000 rows, 51 cols where col 1 is employee number (unique, primary) and the remaining 50 the actual strings (surname, inits, address etc)

- table 3: (amounts) 65 000 rows and 901 cols where col 1 is employee number (unique, primary) and the remaining 900 the actual amounts

OR

 

D) Same as C, but splitting table 3 into say 5 tables.

 

Which of these would be the best design with the fastest access? I like option B), just worried about the (900*65 000) 58 500 000 rows...

 

Any comments? Somebody please advise or refer me to a place where I can get help...!

Thanks very much

[pauper_i]

If you have 10 gallons of water, it\'s going to take the same ammount of space whether you store it in pint jugs or gallon jugs. The trick here isn\'t the space taken by the storage, but rather the speed of access to the storage.

 

Lets look at this logically. From the information you have provided, this would appear to be some kind of payroll system, so lets look at the information based on how often it is going to be used:

 

Employee ID: This is going to be hit every time, for every record, so it need to become the cornerstone of your system. In order to access it, you will probably need first and last names, middle initial (if any), social security number and possibly a department. This will make your first table. I don\'t know what other fields you have in this genre, so you may want to adjust this slightly.

 

Employee data: An employee joins the company and their data is entered. They leave, and it gets deleted or marked as inactive. Uness they move address, change departments or whatever, this data isn\'t going to change much so lump the majority of it into one big table or two smaller ones - the first with data which the employee can change (address, phone, etc.) and the second with data which remains \'untouchable\' to the employee - last pay review, performance review, start date etc.

 

Payroll data: This is going to be hit every pay period (weekly, bi-weekly, monthly) so it needs to be a fast, lightweight table with the necessary information only to allow the payroll calculations to run at optimum speed.

 

Payroll history: This will be where the results of the payroll are stored as a history - it gets appended to by the payroll program, but lookups on it are probably infrequent.

 

With this information, you can now start categorizing your fields and assigning them to their eventual tables. Naturally, all of this is based on assumptions made from what is in your post. To arrive at 900 float fields, you are probably storing a lot of history in there that could probably be better stored in a table rather than in a record.

 

Hope that helps!

 

D

[Dawie]

Thanks so much for the good advice.. I really appreciate it