Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
illumna
Starting Member
4 Posts |
 Posted - 2009-05-13 : 17:52:24
|
| I'm very anal about trying to maintain development standards when designing applications. This puts me in a difficult situation because every company I've worked for let's standards slide and especially in regards to data. This compromise is usually attributed to the lack of time that's available but other times due to the complexity of the data.So, I am again faced with a project that may compromise the relational database model and I would like input from other people who've been in my situation. The situation is that my company currently has several stand alone applications geared towards different customers. Each customer has similar yet different data requirements but we would like to merge all of our separate applications into one application and thus house all of this data under one database. I am perfectly o.k. with this concept but what is the best approach for designing the central database?For example, we have a table for entering time sheets. Company A's table may look like:Customer Number, Employee Name, Hours, Equipment Used, Description, Work Expense, Back Charge, Additional Charge, Service Fees.However, Company B's table may look like:Customer Number, Employee Name, Hours, Equipment Used, Description, Additional Charge, Truck Fees.My boss would like to make an aggregate table that looks something like:Customer Number, Employee Name, Hours, Equipment Used, Description, Desc1, Desc2, Desc3, Charge1, Charge2, Charge3.Obviously, doing it his way would mean that all of the last columns for Company A would mean something entirely different for company B within the same table(entity). I've ran into this situation before and it became quite confusing when resolving data issues. Also, this situation requires a lot of upfront data checking by the UI and prohibits using abstract layering standards. On the other hand, this lowers the amount of tables needed.Is there a better way to design this?I worked with one company that did something similar where they had attribute 1 to attributeX columns with a type field. Depending on the type, attribute1 meant something different in record 1 than 2. This was a mess because all of these fields were integers and required complex joins in order to bring in the descriptions. Then, if we needed to add another bit of info for company A, we had to add another attributeX field and re-program all of the UI's to handle this change.My idea is to have separate tables(time sheets) for each customer as they each have different needs and as each time sheet represents a different entity. If data needs to be aggregated, we can do that using views or stored procedures. My boss is concerned that this would be a maintenance nightmare.Thank You for your input. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-13 : 18:18:31
|
quote:
My idea is to have separate tables(time sheets) for each customer as they each have different needs and as each time sheet represents a different entity. If data needs to be aggregated, we can do that using views or stored procedures. My boss is concerned that this would be a maintenance nightmare.
I agree with your boss. Leave it the way it is rather than go with multiple tables.I'm confused about "each time sheet represents a different entity". If you want to consolidate them under one structure then they have to have something in common. You can centralize these common columns/attributes into a single table, and handle the additionals in either an EAV (entity-attribute-value) structure, or in an XML column. Neither is ideal, especially from a relational database perspective, but it's about all you can do when you have divergent client needs. Only thing worse is to go full EAV on all columns, or XML for all non-key columns.I hate to say this but if they can't or won't get their clients to agree on a common structure, you probably won't be able to consolidate this data. From my personal experience I would give up (and have given up) when I've gotten to this point, and I have several times. I tend to pray they'll move to a completely different structure and then sink my teeth into the design as much as possible. But trying to retrofit something with that many tentacles, nope, I'm done with that.That doesn't mean I can't come up with crazy ideas to help you out though.  Or someone else has better ones. Keep posting examples and we'll see what happens. |
 |
|
|
illumna
Starting Member
4 Posts |
Posted - 2009-05-14 : 09:03:42
|
quote:
Leave it the way it is rather than go with multiple tables.
Well, it isn't anything yet as this will be a brand new application derived from about 15 different applications. I have the ability to make it the way we want from the git-go.quote:
I'm confused about "each time sheet represents a different entity".
What I mean is that in a relational database a table represents an identifiable entity and in that context: Since each company has different data gathering requirements (E.g., timesheets for recording employee time worked and many other things), essentially, company A's time sheet is one entity while company B's time sheet is another. I'm open to suggestion so please don't take my questions as arguments for/against. I'm just trying to understand some things.That being said, what happens when varchar column 'GenericCol1' is being used to store strings, dollar amounts, and integers for the varying customers? No matter the scenario, I will always have to create separate user interfaces for the different customers so that's a given. But now, when formA for customerA saves/retrieves integer data from GenericCol1, I have to cast it to/from a string/integer. FormB may need to cast it to a dollar amount. Then, if I need to sum GenericCol1 to tally total dollar amounts, how does that work? If they are retained in separate tables then I can ensure the type and not have to cast and I can rely on the database to throw an exception if an incorrect data type is stored. Likewise, if I have them in separate tables I can name my column ProductCost instead of GenericCol1. Is that so bad? Why is aggregating the data a better choice that this?Thank You. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-14 : 13:45:23
|
quote:
Why is aggregating the data a better choice that this?
It may not be better. I should have said earlier that your boss' idea of consolidating into that kind of structure is not a good idea either. Using generic columns is not preferable, but neither is having 20+ tables with names like Timesheets_ClientA, Timesheets_ClientB, etc., one for each client.I have to think about an example which I'll try to post later. In the meantime, take a look at EAV modeling and row modeling:http://en.wikipedia.org/wiki/Entity-Attribute-Value_modelhttp://weblogs.sqlteam.com/mladenp/archive/2006/10/14/14032.aspxThe last one shows how XML can work to store variable types and amounts of data in a consistent structure. My idea is to use a hybrid: design all fixed, consistent columns as standard relational tables, and use EAV or XML for the variable columns. |
|
|
|
|
|
|
|
|
Handling Data for Different Customer Needs