Which is suitable SQL database structure in the following case ?

Question

 

Hi All,

I need some suggestion for choosing the database structure for my upcoming project. Please find my system flow and requirements below.

 

 

1. I have a application with different 3 level.
   1. Master
   2. Child
   3. Sub Child

1. Here every control is in Master's hand. Master can add/edit , assign any  attributes[products, category, machines etc.] to the sub child
2. i.e. Master will assign/provide some facility to sub-child and get service money from it.
3. Child level is only responsible for collecting billing and set up the system for Sub-child.
4. Now , this application will be world wise. So we also need to provide kind of culture, timezone, language, currency etc. setting.
5. Master can be at any one country, let's say at USA. And child and its sub child may be at Canada, Mexico, India, China, Australia, South Africa, UK etc.
6. Sub child has also have some local attributes [i.e.  products, category, machines etc.]  which are same
7. Sub child will sell all these above mentioned attributes to the members[clients]  , which are registered with Sub Child

 

Requirements

 

1. Members of the sub child can move to any sub-child within that country to access the facility of attributes for the same
2. i.e. Take a example of USA. If a child at USA have 10 sub child. Member at sub-child 1 can move to sub child 2/3/4/…10 to access the facility.
3. Every member has a bio-metric identity.[fingerprint]. Which he need to check before entering into any sub child to access its service.
4. This bio-metric identity capture machine[hardware] will be at each sub-child.

 

 

• In this case which will be the suitable database structure. I need the solution in SQL Server 2005 / 2008.
• I have thought on some of the below DB structure
  • Local DB at each sub-child and a Master DB. Sync process for data synchronization
  • Country wise local database and a master DB, i.e. each country will have a database regardless of number of Child or sub- child
  • Single DB world wide

 

 

Hope I have explained it properly. Any suggestion would be appreciated.

Thank you

Answer 1

From your description, I prefer to have one database for all data (master, child, sub child) for each country (similar to the second solution you mentioned) if network bandwith is available.

No replication headache within the country.
Same cultrure within the country
No replication requirements with other countries. 

Answer 2

Hello Phe,

Thank you very much for the reply.

One thing I want to mention here is , within a one country culture may be same [i.e. in USA it is en-US] , however timezone and language may be differ.

I am in search of some of the case study/documents/articles or anything from where, I can find the countrywise database structure's overview and its pros and cons.

Anyway thank you again. Your reply really helpful. :)

Answer 3

Could you supply a few more examples? I am having a difficult time understanding what this is for. It simply hasn't "clicked" yet.

Answer 4

·          In this case which will be the suitable database structure. I need the solution in SQL Server 2005 / 2008.

 

Answer: I would recommend you to use SQL Server 2008 as you will get more advantage of new technology and the performance would also best.

 

·         I have thought on some of the below DB structure

1.      Local DB at each sub-child and a Master DB. Sync process for data synchronization

2.      Country wise local database and a master DB, i.e. each country will have a database regardless of number of Child or sub- child

3.      Single DB world wide

 

 

Option 1:

This option is acceptable but you have to consider few things for the database maintenance

1. The status of synchronization of Master DB and Local DB.

2. The synchronization time from master db to local db will differ as per the business hours

3. Database growth for fact (transaction data) and Cube processing time if Reporting is present

4. If some thing new to be done then the changes to be done by all local db this is very hectic task.

5. Yes maintenance also matters a lots.

 

Option 3:

This option is acceptable but you have to consider few things for the database maintenance

 

1.       Size of Database

2.       If country wise business differs

3.       Yes, time zone and business hours changes.

4.       Country wise business can not be handled as per requirements

5.       Cube processing if reports need to be generated.

 

Option 2:

This is most usable method by business and benefits bellows

 

1.       Logic for Country wise can be implemented

2.       If new changes comes that is easy to make changes for the whole country.

3.       Database size can be handle with proper management of data

4.       The data can be processed as per the business hours.

5.       Cube processing will be same for country wise.

 

But one issue if Country is more than 100 then you can not think about this.

Then we need to think about the region wise if business same like

“USA, “Europe”, “Asia”, “SA”

 

Finally I would suggest you to go through properly about the business needs and try to figure out the best solution which can best suits for your business..

 

Thanks,

Sandeep

Answer 5

  Hello Brian, 

I need to build a database structure for a world wide application like McDonalds.

 

Let's consider the case of McDonalds food chain automation

 

1. McDocnald head office at USA , It is a controller of the whole world wide business chain. In our case it is a Master

2. Let's say there is one franchiesee at UK[i.e. Child]

3. Now UK child has 3 sub-child [like a retail shop] at let's say at London, Bridgend, Lancashire.

 

4. Now Master at USA has some define food like pizza , pasta etc.

5. At every sub-child at any country may have[may not have] these food define by master , as well as thire local food also.

Hope I have explain the proper example. :) . Now you can read my original post, hope it is clear now.

Answer 6

Hi Sandeep,

Thank you very much for the descriptive answer.

It is  really easily understandable  and to the point.

I am currently checking the feasibility for the all options.