Database Architecture - Handling Data for Different Customer Needs

[illumna]

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 (or doesn't care) 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 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.

However, Company B's table may look like:
Customer Number, Employee Name, Hours, Equipment Used, Description, Additional Charge.

My boss would like to make an aggregate table that looks 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 fixing data issues. Also, this situation requires a lot of upfront data checking by the UI and prohibits using solid abstract layering standards. On the other hand, this lowers the amount of tables needed and makes updating a cinch (possibly).

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.

Thank You for your input.

[saurabh.agarwal]

An option could be maintaining an index i.e. table with all the field names and a separate table containing the client specific name and the corresponding master field id. Use a third table to store the actual data corresponding to the client specific field id.

[jobzesage]

Hi illumina,

I am in a similar situation, yet a little more tricky maybe. I am working for several organizations, who have several databases with people. From their internal workers, to members, newsletter subscribers, partners,... all in all, quite a mess since everyone is using different tools, and no one is really happy about they have.

So I went on a quest of finding an address book sort of web app, that would be simple to use, and extremely flexible/scalable to reach all the needs. Mission Impossible, I didn't find anything. We've decided to launch our own Open Source project, and I am currently designing the web app.

But I am running in the same problem as you are, the whole point of it is that each administrator can build his own data structure from the user interface, without any coding. But my plan is to go further, allowing the use of plugins to define data types, and means of exporting the data. This would allow you to build all kinds of systems fitting the needs.

If you want to know more about it, you can read about it on our sourceforge project page, project named "adres"

But here is the design challenge !
--------------------------------------

To keep it flexible, I would like to let the plugin be responsible for his set of data. The plugin becomes therefore a black box. It can process a search, return a string to display it's value in a grid,... plugins are PHP classes that will extend an Interface called Field.

Now, it's much better to let MySQL do the filter and the sorting rather than trying to do it in PHP since MySQL is optimized for that. But plugins are PHP black boxes. So if I wanted to display all the data, I have to go through each contact, and for each field call the getString function.... That's plenty of SQL requests.

Better, I can ask a plugin to send me all the values for a certain Filter. In that way I can extract the datas column by column. But it makes sorting a little difficult... But so far, it seemed to me that this was probably the best solution. Giving a lot of freedom to the plugin developers.

But then I had an other idea, what about having the plugins sending back the information required to build one SQL request. They would send me the name of the table, the connecting field name, the parts that goes in the SELECT",... That would make searching and sorting a lot easier, and could be processed in one request. But is that really a good idea ?!? It sounds a little pulled by the hair in one way, but maybe it's the best way.

It feels like I am really mixing the logic and data layer, which is normal in one way since I want a dynamic data structure. But I just wonder if I'm not missing a point there.

Any suggestion ???????

If you wanna dig more into that question, check out the document called documentation.pdf on Sourceforge. I am discussing some of these options in a deeper way. But please forgive me for the quality of the writing, it's a working document, not a publication ;-)