Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have a distributed data collection system where I have first layer as regional Databases where all collected data is initially stored and master database where all the data is consolidated, what are you thoughts on schema design of this situation, also important thing here is I could have overlapping Ids and dictionary tables are shared by all regional db and also in future there could be need to add new regional Dbs.

I am not sure how to start on designing the schema for this situation and would like to discuss with SO Community ?

share|improve this question
add comment

migrated from stackoverflow.com Mar 21 '11 at 7:08

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 5 down vote accepted

If the natural keys are hard to identify, you can still use surrogates for both regional databases and the master one.

For example, regional database schema:

CREATE TABLE main_table (
  region_id INT NOT NULL AUTO_INCREMENT,
  region_name CHAR(30) NOT NULL,
  [other fields],
  PRIMARY KEY (region_id)
);

The master database schema would look like this:

CREATE TABLE main_table (
  id INT NOT NULL AUTO_INCREMENT,
  region_id INT NOT NULL,
  region_name CHAR(30) NOT NULL,
  [other fields],
  PRIMARY KEY (id)
);

The trick is to have every regional system to supply unique value for "region_name" field. Then each system will happily collect data and no problems will happen during aggregation. The master database will have its own unique IDs and there is going to be a reference to the origin of data (region_id, region_name).

The biggest headache with this approach is references to other tables. When you copy data from regional to master database, you will also have to employ similar approach to other tables, which is, as I said, a headache.

Another way is to use a compound PK:

CREATE TABLE main_table (
  region_id INT NOT NULL AUTO_INCREMENT,
  region_name CHAR(30) NOT NULL,
  [other fields],
  PRIMARY KEY (region_id, region_name)
);

This way the table is going to look exactly the same between regions and the master. At the same time, compound keys is a headache on its own.

As I'm writing this answer, I understand that there is no easy solution, as always :)

Update:

If a natural key is used then there is going to be one unified schema:

CREATE TABLE main_table (
  id CAHR(32) NOT NULL, -- most likely ID is going to be a string, not number
  region_name CHAR(30) NOT NULL, -- if you want to keep the info about the origin of data
  [other fields],
  PRIMARY KEY (id)
);

The idea of a natural key, that it's unique regardless of a region. In books, the usual examples are Social Security Number, Passport Number, etc.

You can have a simple hybrid solution. The schema stays as depicted above, and you calculate ID values yourself:

UYT49.2873645
UYT49.2873646
UYT23.7824

The first part of the string is your region id, the second part - auto-incremental number. As a whole, the string is globally unique which is what you want.

share|improve this answer
    
Let say if I want to make use of Natural key then how would solution change ? –  Rachel Mar 20 '11 at 22:46
    
@rachel I updated the answer –  Yuriy Zubarev Mar 20 '11 at 22:58
    
@Yuriy - You say the natural primary key is probably a string but that does not have to be the case; a composite key can also be natural. –  Tony Mar 21 '11 at 0:08
add comment

You might consider using a GUID as your primary key for each entry in the database. When your regional databases are merged, you wont have to worry about duplicated id's.

share|improve this answer
add comment

Try to identify natural keys from your data, they will not cause the same problems as surrogates when you come to merge your data.

share|improve this answer
    
Tony, can you elaborate more on your suggestion so that I can understand from Design perspective, right now I am not able to get much out of your suggestion. –  Rachel Mar 20 '11 at 22:06
    
Apologies for not elaborating, I posted my quick answer then was off-line for a while but it seems Yuriy's answer has provided you with what you need to know. –  Tony Mar 21 '11 at 0:06
    
Tony, if you elaborate and I get better understanding than it would be very helpful. I will wait to see your updates. –  Rachel Mar 21 '11 at 1:56
add comment

As suggested, use any UUID implementation, GUID being one of them. It practically guarantees that the ids you generate are unqiue. More at http://en.wikipedia.org/wiki/Universally_unique_identifier

The benefit of using it among others is - most of the programming languages/ technologies/frameworks/databases supports it, as it is an industry standard (RFC Link : http://www.ietf.org/rfc/rfc4122.txt ). Excerpts from RFC ...

  1. Motivation

    One of the main reasons for using UUIDs is that no centralized
    authority is required to administer them (although one format uses IEEE 802 node identifiers, others do not). As a result, generation on demand can be completely automated, and used for a variety of purposes. The UUID generation algorithm described here supports very high allocation rates of up to 10 million per second per machine if necessary, so that they could even be used as transaction IDs.

    UUIDs are of a fixed size (128 bits) which is reasonably small
    compared to other alternatives. This lends itself well to sorting,
    ordering, and hashing of all sorts, storing in databases, simple
    allocation, and ease of programming in general.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.