Databse 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

😎 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 😎, 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

[drawmack]

option b is close:

4 tables

1) employee information - this table contains just the basics. Everything you need to make a check out to the person should be in this table.

2) employee numbers - the floats

3) employee dates - duh

4) additional fields - most the the additional fields.

[bastien]

generally there should be a series of lookup tables that take the rest of the application refers to to get basic data...this data should be none-volatile (ie does not change much, like:

emp: names, social insurance numbers, birthdates;
addresses: which really only change infrequently;
dates: date joined etc)

then transactional data is usually stored in a big heap table where its all mixed together (you could consider altering the table to separate the fields by information type like taxes, salaries etc but that may more driven by the application requirements)

the number of tables is never really an issue, you require as many as you need to get the job done...space is more the issue but hard drives and ram chips are cheap nowadays

hth