January 18, 2010 at 1:00 pm
Hi all,
I was wondering if someone could help me think of some idea's, consider the following scenario;
You have a table, lets say it list a series of employee, it contains things like id, name, etc.
However, the table has sadly gotten very wide (25 columns or so), and a new requirment to store the employee's department has arisen, you have the following options to store this data, and potentially other new data columns. Once the data has been inserted, it's unlikely to change.
1. Add the column on to the employee table, but not all employee's have a department (you'll have to imagine this) so you'll be storing a lot of nulls around (70%). This column would map to a new department table. This is ok, but when we go and add new data columns, these logically should have a new table as well, meaning we're going to having to look after an ever increasing number of tables.
2. Create an attributes table, and insert one record for 'department', then in a new mapping table you could map the employee id to the attribute id and then define the value, this would be really flexiable as you can add new attributes easily and it's easy to get all the attributes for an employee, but if users want the attributes back as a columns, you could end up doing multiple SELECT's, although you could implement a cache table to get round this.
3. Create an employee detail record, and have the employee record, then an employee detail record, the employee record stores the summary top
level data, and the employee detail record stores the remaining additional data, in individual columns.
Hope I've explained myself well enough, I'm just really looking to see if anyone has a better way of doing it.
Any help would be appreciated, I can see pro's & cons of all, but I was just looking to see if I'm missed something.
Thanks in advance. 😀
January 18, 2010 at 2:09 pm
No additional ideas. Just a comment. I would go with option 3. This one seems to make the most sense.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 18, 2010 at 2:30 pm
Thanks for the reply.
Thats how I would typically do it, it's just the fact that some employee's may have attributes (columns) that others may not that makes me thing option 2 would be good as your table wont be littered with NULL values, as you would only be storing what you need. Although then you have additional complexity's in returning the data.
Oh well, option 3 it is for now then.
Thanks.
January 18, 2010 at 2:36 pm
NP.
Sidebar - Option 2 poses potential performance problems with it too. I have seen bad implementations of this that killed the database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 18, 2010 at 2:43 pm
I tend to agree with #3 as well
January 18, 2010 at 2:52 pm
For anyone who is interested I came across this;
As my decision was being influenced by the volume of NULL values, it helped to clear a few things up. 😉
Thanks.
January 18, 2010 at 2:54 pm
Steve Jones - Editor (1/18/2010)
I tend to agree with #3 as well
Thanks for the reply Steve.
3 it is, just thought it best to check, sometimes you cant see the forest from tree's, and all that. 😉
Thanks again to you both.
January 18, 2010 at 3:01 pm
MrT-SQL (1/18/2010)
For anyone who is interested I came across this;As my decision was being influenced by the volume of NULL values, it helped to clear a few things up. 😉
Thanks.
You're url will display properly if you add text between the ] and [
i.e. columns][/url]
would be columns]Read Me[/url]
Just an FYI.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 18, 2010 at 3:01 pm
MrT-SQL (1/18/2010)
Steve Jones - Editor (1/18/2010)
I tend to agree with #3 as wellThanks for the reply Steve.
3 it is, just thought it best to check, sometimes you cant see the forest from tree's, and all that. 😉
Thanks again to you both.
You are welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 18, 2010 at 3:07 pm
Stay away from #2. That's an EAV (Entity/Attribute/Value) model.
It saves a little work up front in data modeling by allowing "open ended" insertion of new attributes at the cost of having to program the true data structure into each query. Of course, there are other annoying little problems, like enforcing not null, DRI, domain integrity, default values, check constraints, creating useful indexes, transactional integrity, etc. Basically, it takes all the most useful features of a relational data model, and throws them away.
In practical terms, what you get are nightmarish queries with 40 left joins, horrible performance, query plans that look like integrated circuit diagrams, impossible to debug data integrity issues, phone ringing off the hook, hours spent looking at a query window hoping that stored procedure runs in less than 2 days this time while everyone stands around asking why you can't just fix it.
January 18, 2010 at 3:17 pm
Michael Valentine Jones (1/18/2010)
Stay away from #2. That's an EAV (Entity/Attribute/Value) model.
Hmmm...And I thought EAV meant Extremely Abominable Virus 😀
In practical terms, what you get are nightmarish queries with 40 left joins, horrible performance, query plans that look like integrated circuit diagrams, impossible to debug data integrity issues, phone ringing off the hook, hours spent looking at a query window hoping that stored procedure runs in less than 2 days this time while everyone stands around asking why you can't just fix it.
Don't forget the countless hours at the Psychiatrist 😀
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply