04-11-12, 14:37 #1Registered User
- Join Date
- Apr 2012
Unanswered: Multiple DB versions, intermittent connection
Thanks in advance of any help/advice you're able to offer. I'm not looking for a detailed, full solution but just some pointers and thoughts on any pitfalls!
I'm working on a DB that will be used in separate physical locations. These DBs will be identical in structure and completely independent of each other. Whilst some tables will retain similar or identical information, there is a large amount of content that will diverge.
However, there is a central location that will need to manage these DBs. There will be a requirement from this central location to run reports, often comparing data between them, and to push data into each child location. There is no requirement for the child locations to 'share' information and indeed the volume concerned and the intermittent/low-bandwidth nature of their connections would make this undesirable. If an individual selection of data does need to be shared between I'm happy for it to be uploaded centrally, a decision made, and manually pushed to each child-location.
Which brings me to my real question. Should I try and include composite Primary Keys in many of the tables (to include probably an incrementing number and another field of site ID) so that when I absorb them all back centrally I can put them into one large DB. Or should I keep them as separate DBs in the central location and use some fairly 'normal' mechanisms for mirroring to sites; accepting that this will make running comparisons between them more difficult and potentially more difficult to write interactivity for centrally?
The best 'real world' comparison I can think of is transactions in retail shops. How do these transaction logs get stored centrally when they're all generating individual transaction numbers that are only unique in that location?
Thanks in advance of your suggestions.
04-12-12, 12:04 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
You have two choices that I can see.
I'd use a GUID for the primary key, then use whatever other columns you need or want. This allows you to define meaningful natural keys using whatever columns make sense, such as a transaction number, etc. Keep in mind that in this case the GUID is really a surrogate for the business key, they both uniquely identify a row (GUID for your code, natural key for your users).
Your idea for using a pure natural key composed of both a "system id" column and some other column or columns would work too, but there can be issues using it. Every bit of code that touches these tables needs to keep the full key in mind, which isn't all that hard for your IT staff but will quickly come to bamboozle your users if they ever get to the point that they can write SQL queries.
Based on my past experience, I'd argue strongly for a single column that is meaningless to the user for the PK. If they can't easily make sense of it, they probably won't try. This will protect them from making many of the "newbie" mistakes until they have enough sophistication to deal with the data.
I'd also insist on using UTC times for everything. As you expand and get sites in different time zones this becomes more and more important, and it is almost always either completely overlooked or pooh-poohed until it is too late to fix it.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.