Multi-user / customer design - SQL Server Forums

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Multi-user / customer design

Author

Topic

dave_winchester
Starting Member

6 Posts

Posted - 2011-09-15 : 06:43:13

Hi all

My first post here, so please be gentle and I apologise in advance for any etiquette that I miss.

I am at work designing a new version of an application that in the past has been primarily a single customer design i.e. a customer buys product "x" and then we install that software & database either on their servers, or host it ourselves. But, it has always been one customer per database. This meant any changes to content was isolated to that customer. OK, background set!

I now need to build a new version that can support multiple customers, my problem is with the design. We have around 80 lookup tables with things like "ArticleCategory, LeadStatus, CampaignType" etc... which in the past the customer could customise from the default system values.

By default all these tables have some basic data. The customer can then change this to suite their requirements.

My questions is with the design. I have added an OrganisationID to these lookup tables which will allow the queries to return the customer specific values but, if the user does not create or alter these values then this remains NULL. So therefore this would not return anything.

Do I for a good design and implementation of this setup:

- A: Return all values that have OrganisationID of NULL, this means that these are default system values. Then, when the customer edits these values, create a whole new set based on the original values plus any amendments to the list.

- B: When a customer registers, create all these default values again with the customer ID set against them. My main concern is that if I have 1000 customers (lets say busy is GOOD!!!), and they never change the default values - that could be alot of extra data for nothing.

Any help, guidance or help would be so greatly appreciated. I can't find any articles that cover this type of design.

Many thanks in advance.

David

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-15 : 07:29:49

These customer specific data...will they be instead of the default data or in addition to the default data?

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

dave_winchester
Starting Member

6 Posts

Posted - 2011-09-15 : 08:20:22

Hi - Thanks for the reply.

It could be an addition to what is already there, an update to an existing field e.g. Started --> Launched or even removal of a value that is part of the default list.

I think (after some more looking around), what I am after is how to design a "picklist" but don't know where to start.

Many thanks

David

Kristen
Test

22859 Posts

Posted - 2011-09-15 : 08:32:40

We do this with two tables. The Defaults and the Customisation.


SELECT [Col1] = COALESCE(C.Col1, D.Col1), [Col2] = COALESCE(C.Col2, D.Col2), ...
FROM dbo.MyDefaults AS D
     LEFT OUTER JOIN dbo.MyCustomisations AS C
         ON C.ID = D.ID
        AND C.OrganisationID = @OrganisationID

that way if you want to add a new Default value, you can.

This only works provided that the customer can't set addition values, for which there is no Default defined.

You could do this with a single table, using a Self Join, if you want. best not to use OrganisationID of NULL because that will prevent you having OrganisationID in the Primary Key

dave_winchester
Starting Member

6 Posts

Posted - 2011-09-15 : 10:55:14

Hi Kristen

Thanks for your suggestion. But, we will need to allow to add, remove options not just rename them. I did a test of your suggestion and it works perfectly for another situation I have - so many thanks. But, I am still stuck with the original problem.

Regards

David

Kristen
Test

22859 Posts

Posted - 2011-09-15 : 11:04:23

If you want to Add/Delete then you can store that "Mode" in a column in your MyCustomisations table.


SELECT [Col1] = COALESCE(C.Col1, D.Col1), [Col2] = COALESCE(C.Col2, D.Col2), ...
FROM dbo.MyDefaults AS D
     LEFT OUTER JOIN dbo.MyCustomisations AS C
         ON C.ID = D.ID
        AND C.OrganisationID = @OrganisationID
WHERE (C.ID IS NULL OR C.Mode <> 'DELETE') -- Either no customisation exists, or the customisation is not marked as a DELETE
UNION ALL
SELECT C.Col1, C.Col2, ...
FROM dbo.MyCustomisations AS C
WHERE C.OrganisationID = @OrganisationID
      AND C.Mode = 'ADD' -- User-added customisation. Make 100% sure does the PKey does not conflict with any Defaults added in the future!

Kristen
Test

22859 Posts

Posted - 2011-09-15 : 11:05:05

[Mode] column ought to be TINYINT or somesuch, VARCHAR(lots) is not the best datatype!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources