Originally Posted At: http://weblogs.asp.net/alex_papadimoulis/archive/2004/05/20/136018.aspx

I suspect this one may be a bit less common than tblState ...

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.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!