Help with database design!

Anon

Hi all!

I would like to ask you guys a question regarding the design of one application I'm involved with.

We are preparing a basic software for some different clients. However the application needs to be changed by its owner.

This implies each end user may be able to add columns (or tables) to the system. However and to avoid communication issues, only the basic information should be
transferred.

Up to now, some ideas has come into my mind, like:

adding additional tables for the extra columns the user wants to add.
Add the extra columns to the existing tables and create a view with the common columns to everyone.

Do would you suggest? Any ideas?

Thank you very much in advance.

David.

Anon

Are your customers going to have free reign to add as many columns as they like?

Personally I like the extra table idea, it keeps the common data clean and give less work to the programmers to incorporate it.

[deleted]

"Personally I like the extra table idea, it keeps the common data clean and give less work to the programmers to incorporate it."

If the new columns are just additional information that adds to the columns that are already there, like for a products listing where you have the price in dollars and you want to add the price in euro, then I would definately go for extra columns in the existing table.

An extra column is much easier to incorporate than a new table (which would require a JOIN for a nasty one-to-one relationship)

If you use a proper VIEW, you won't ever know that the extra tables are there anyway.

Anon

True, but if you have no control over what or how many columns the client is going to add then building in some generic controls to search for and access data from a secondary table is fairly easy.

Plus now you don't have a single table that is holding your common data plus all of your clients different extra data.

I agree if it's just a single column then that is easy enough, but if it is a single column why not adjust the design to make this column part of the common data?

[deleted]

I think the entire point is that there are a few basic columns, and the end-user can add new data to make the app fit their needs.

In that case, the new columns are already part of the design to begin with.

At any rate, adding a new table would just force a one-to-one relation which is nonsense.

Anon

Don't like 1 to 1 relationships do you vincent? ;-> Anyway while I agree with you in theory, what I'm hearing is that there is a common database (I believe with common data) that is shared by all clients and they are allowed to add there own columns and data to make it fit their needs.

The way I'm seeing it is that this is an on-line database that holds the common data (owned by the main company) and each of the clients gets access, probably read only, to this data. But the clients want the ability to add information that they own to each of the common records so they need some kind of write access. Now I wouldn't want to let the clients mess with the common data since if one screws something up it screws things up for everyone. And since I don't want to have to be incharge of the clients data I'd be willing to build a 1 to 1 relationship with another table to let them have their data.

[deleted]

Ok, enough of this.

David, what exactly IS the idea?
what is the table used for and what are the new columns used for?

donvito87

First of all let me thank your interest and support.

As far as I can tell you, there will be a common schema for all five clients. Apart from that each end user will add its own features. In some cases it might be a 1 to 1 relatioship but this may not always apply.

However we want and need to have communication among those five users with the common structure for everybody.

My first idea was to add columns to the given table and then create a bunch of views which will be accessed to communicate with any other end user.

You are also right we need to consider the number of possible occurencies a new column may have. Let's say I have a table like EMPLOYEES. If only an end user wants to add record information regarding let's say previous jobs, then the best solution would be to create a new column with a foreing key.

What do you think guys?

Thanks.

David.

Anon

You didn't answer the one question that I was concerned about. Are your clients going to be owning there own copy of the database and data, or are they all going to be sharing a network copy of some tables/data?

If your clients are going to be owning the database and data and you are just customizing a baseline database then just adding columns to your tables is fine since each of your clients will get a customized version of the product. I worked for a company that did just this and what we did was keep a baseline product that did everything that we expected it should, and made modifications to each clients version. When a client asked for a change that we felt should be made to all clients we made the change to the baseline and then offered the change to all of our other clients.

If your clients aren't going to be owning the database/data for your common information then you need to figure out a way to make client specific changes that aren't going to affect your other clients.

donvito87

Thanks Rob!

Sorry for not answering your question. As far as I'm concerned each client will own its schema. It will actually by mantained by them.

The idea is for them to be able to add new functionallity without our intervention. I know it may sound a little bit confusing, but that's the way the system is meant.

If we take into consideration the fat the client can modify the database schema as much as they want, I don't believe that by adding a new column into the base table would work.

I'll keep you guys posted about my design.
If you have any suggestion, please let me knon.

Thanks for your cooperation.

David.

Rob wrote:

Anon

Ouch, you are giving DBA permissions to a client who isn't a DBA.

Good luck. I'm guessing that you are going to have to build an entire GUI table management system to allow your clients to work with the tables, and then you are going to have to enforce some rules on what they can do so that you can maintain data integrity. Otherwise they'll be calling every week when a change that they made doesn't work and you'll have more work on your hands than just changing it for them by request.

donvito87

Rob,

I think you are more than right. The scenario is not the best, but i will have to handle it.

Thanks for your help.

David.