Database design ... WTF indeed :)
Posted
Friday, May 21, 2004 4:57 PM
by
Mischa Kroon
When reading up on http://weblogs.asp.net I came across the following Database design suggestion:
“Used on an in-production, web-based staff directory of 600 or so, the idea behind tblStaffDirectory is expandability. By putting everything in one table, we don't need to worry about ever having to modify the database structure. Data in this table is stored in the following manner:
| ID |
intID |
strTable |
strField |
strValue |
| 1 |
1 |
Staff |
StaffID |
1 |
| 2 |
1 |
Staff |
First Name |
John |
| 3 |
1 |
Staff |
Location ID |
4 |
| 4 |
4 |
Locations |
Location ID |
4 |
| 5 |
4 |
Locations |
City |
Anytown |
Creative, eh? While queries to retrieve the data in a useful manner may be a bit complicated (120 lines and 38 JOINS to get the equivilant of “SELECT * FROM Staff“), it's a heck of a lot better than the alternative (changing the table structures). Besides, I don't think users mind waiting 5-10 seconds for the page to load. I challenge anyone to come up with a better implementation than this.”
I disagree that this would be a good solution to this problem huge sql statements can hardly be a good way to do things in a relational database kind of way.
This table is also in no way normalised table and field are strings and are redundant a much better and cleaner way to solve this would be the following:
Staff_tables
- id
- name
Staff_tables_fields
- id
- name
Staff_departmentname
- id
- locationid
- firstname
- lastname
- city
- etc
If all the departments have the same table structure the Staff_tables_fields table can even by avoided.
Since the example was in a staff_tables table can also be avoided because the tables can be queried with a simple sql command this list can also be selected with a where statement limiting the resultset to just tables with “staff_*“
This eleminates the possibility of typos in the tables and fields.
Advantages:
- The database structures are clear and easier to maintain.
- The speed should be going to normal levels.
- Errors in the database are avoided.