ianmansell01
asked on
International Database Design - Single Database To Hold All Countries
Hi All,
My question is about design / patterns for an international database.
The client has requested a single database to allow for many countries to enter their data (English, French, Japanese, etc). Each country could be viewed as a separate system almost.
All lookups / pick lists must be divided by country (easy).
Where is the best place to identify a countrys ownership of data?
The best way to describe what I am asking is on the attached example ERD... this is an example not the real thing!
Many thanks for help.
Ian.
xDBExample.png
My question is about design / patterns for an international database.
The client has requested a single database to allow for many countries to enter their data (English, French, Japanese, etc). Each country could be viewed as a separate system almost.
All lookups / pick lists must be divided by country (easy).
Where is the best place to identify a countrys ownership of data?
The best way to describe what I am asking is on the attached example ERD... this is an example not the real thing!
Many thanks for help.
Ian.
xDBExample.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Excessive duplication of data of references to data is a mistake as it bloats your data structures. So, don't assign directly a reference to ISOCountry to all of your sub components.
It seems an Information Expert pattern question: who holds initially the information you/the system need to obtain a country data? I belive, looking at the picture, that xLocation would be a possible candidate, but I lack a lot of informations. What is Structure, Components? Which are their responsibilities?
It seems an Information Expert pattern question: who holds initially the information you/the system need to obtain a country data? I belive, looking at the picture, that xLocation would be a possible candidate, but I lack a lot of informations. What is Structure, Components? Which are their responsibilities?
ASKER
HI ai ja nai,
Many thanks for the response. The tables are just a made-up example...
The question is were do you *efficently* place country FK (SystemInstance) in the table hierarchy. As Daniel Wilson suggested, the highest level of each hierarchy.
All the look-up/reference tables will require a country FK (SystemInstance) in order to divide-up the country's own data.
The issue is having to join 5+ tables from the bottom up just to find out a row belongs to "Belgium" for example.
Just wondered how other people approach this kind of design..
Cheers
Ian.
Many thanks for the response. The tables are just a made-up example...
The question is were do you *efficently* place country FK (SystemInstance) in the table hierarchy. As Daniel Wilson suggested, the highest level of each hierarchy.
All the look-up/reference tables will require a country FK (SystemInstance) in order to divide-up the country's own data.
The issue is having to join 5+ tables from the bottom up just to find out a row belongs to "Belgium" for example.
Just wondered how other people approach this kind of design..
Cheers
Ian.
Well, I would not put it in hierarchy terms. I'd put references to that information only at the one who has convenience to own that information. The others may contact him to get to know that info.
For example, a store must know his own street; but the products this store sells don't have to know it. They may know they are being sold in that particular store, and this gives you the ability to get the street name with a product in hand (product-->store-->street asociation), without actually providing the street attribute to every product :)
For example, a store must know his own street; but the products this store sells don't have to know it. They may know they are being sold in that particular store, and this gives you the ability to get the street name with a product in hand (product-->store-->street asociation), without actually providing the street attribute to every product :)
ASKER
Hi ai ja nai,
I agree with what you say but in my example:
Country > Contracts > Places > Structures > Panels > Faces
A [Contract] is based in a country. The country is associatied all the way down the hierarchy but it would be a little "odd" to place a [Country] FK of on all the child tables?
A [face] does reside in a [Country] as does a [Panel], [Structure], etc - where would you assign the [Country]??
Many thanks.
Ian.
I agree with what you say but in my example:
Country > Contracts > Places > Structures > Panels > Faces
A [Contract] is based in a country. The country is associatied all the way down the hierarchy but it would be a little "odd" to place a [Country] FK of on all the child tables?
A [face] does reside in a [Country] as does a [Panel], [Structure], etc - where would you assign the [Country]??
Many thanks.
Ian.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi ai ja nai,
Thanks for the comments. So you would agree the answer is essentially what I/Daniel suggested.
The very nature of providing a single path from parent to children produces a hierarchy, the technique used is relational?
I will awared both yourself and Daniel the points for your responses.
Thanks, Ian.
Thanks for the comments. So you would agree the answer is essentially what I/Daniel suggested.
The very nature of providing a single path from parent to children produces a hierarchy, the technique used is relational?
I will awared both yourself and Daniel the points for your responses.
Thanks, Ian.
Yes, it's relational because, even if you navigate the relations from bottom to the top, you did not build any tree structure. You have the impression of nested tables because of the very nature of contents (big components with smaller components inside). Try to imagine all there tables as horizontal or sparse and you'll se that we just used a relational property of linking data. I know I'm not so good at explaining :)
Anyway, yes I agree with you :) Assign it to the one who holds the information, i.e. the highest "level", and the ask him for which country something belongs to.
ASKER
Many thanks for you reply. *Sorry of the delay in mine...
Seems like a sensible wayforward... anyone else experiance in this area?