SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Global Customers, Global Data, Different Logic/Columns per country


Global Customers, Global Data, Different Logic/Columns per country

Author
Message
The DBA-444918
The DBA-444918
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 245
Hi Guys,

We are working on a project to redesign our existing database.

We have customers which have access to specific country data based on their subscription.

The users will get access to features at a global level but the logic may be different from country to country.

The users will be able to take notes amounts other features against this country specific data.

Some of these notes etc are against Location specific data for the specific country, and analysis may need to be made using this specific locality data.

This data may need to be displayed in an aggregated fashion at global level in the ui based on common columns

My Question is relating to the Users Notes/Data stored for analysis ..

I see a bunch of different options, I just want to make sure we take the correct design decision

1) Create a table with all the specific columns requires to store all specific country data, but make 70% of the columns nullable

2) Create a table with and reduce the number of columns by using non specific column names (eg LocalityLevel1, LocalityLevel2, LocalityLevel3), create views over this table with the country specific mappings, insert and retrieve the data using the views.

3) Create a parent table with a set of child tables per country with the country specific columns in each child table. The parent table will have a country column, this can be used when retrieving the full details of the record

Thoughts?
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25521 Visits: 12494
There isn't enough information to say for sure what the best approach will be in your particular case, but there are some general approaches to designing relational schemas that lead to the comments below.

I don't like your option 2, because I've seen people get into an awful mess with that approach. It makes it much easier to write bugs than ether option 1 or option 3, and that alone would incline me to reject it. It certainly isn't relational, and it can lead to unpleasantly complex queries with poor performance.

I guess the ability to declare a table "sparse" may make option 1 reasonable, but personally I would probably prefer prefer option 3 if there are a large number of columns involved. A lot of people are dogmatically against any use of nullable columns, but in my view there are cases where they are justifiable, so I wouldn't rule option 1 out altogether, but I do feel that nulls should be avoided unless there is a very good justification for them and I don't see one here on the information you have provided (I also don't see that there is definitely no such justification).

There is a fourth option: instead of a parent table, make each country-specific table have the columns that would be in the parent table as well as the country-specific columns, and have a view which is the union of the restrictions of the country-specific tables to the "parent" columns (one of which is a country identifier, and is constant within each country-specific table). As that view is updateable without requiring a trigger, you can do anything with that view that you could have done with a parent table without accessing a country table, and you avoid the joins that would have been needed to get the complete picture for individual countries; the downside is that the working set for country-independent working may be larger that it would with a parent table; that of course can be eliminated by indexing the view, but that creates a disc space overhead.

To choose between the 3rd option and the two variants of the fourth option requires an exercise in performance evaluation (covering disc storage space, RAM storage space, IO volume, and CPU usage); but I believe that with any of these three you are probably going to have less complex code than with either of your first two options.

Tom

cbarus
cbarus
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 160
My personal preference would be option 3, provided you have finite number of countries and they dont keep changing frequently.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search