June 7, 2013 at 11:57 pm
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?
June 8, 2013 at 5:26 pm
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
August 16, 2013 at 10:19 am
My personal preference would be option 3, provided you have finite number of countries and they dont keep changing frequently.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply