[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

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
0
ianmansell01
Asked:
ianmansell01
  • 5
  • 4
2 Solutions
 
Daniel WilsonCommented:
As you've suggested, I would assign the country at the highest level of each hierarchy.  there will probably still be several tables in which you need the country code / ID -- but I would put it in an Order, but not every OrderDetail, for example.
0
 
ianmansell01Author Commented:
Hi Daniel,

Many thanks for you reply. *Sorry of the delay in mine...
Seems like a sensible wayforward...  anyone else experiance in this area?
0
 
ai_ja_naiCommented:
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?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
ianmansell01Author Commented:
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.
0
 
ai_ja_naiCommented:
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 :)
0
 
ianmansell01Author Commented:
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.
0
 
ai_ja_naiCommented:
Nowhere. You don't need to.
In the sense that a panel should have a reference to the structure to which this panel belongs. And given a panel may know in which structure is. A structure, of course, has a reference to his own place. And  a place knows his contract. And a Contract knows his country.
So, recursively navigating from a reference to another, you have got which country a panel belongs to, without adding any filed in any place.
Think relational, not hierarchical
0
 
ianmansell01Author Commented:
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.
0
 
ai_ja_naiCommented:
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 :)
0
 
ai_ja_naiCommented:
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now