Hi all,
I'm currently designing a system that has many databases (1 at each
"site"), and the data from each site will be periodically backed up onto
a single centralised database. The centralised database will also allow
users to access the data via the Internet.

Each "site" has a unique alphanumeric identifier. I use this to
uniquely identify the "site" in the centralised database by making a
compound key out of each table's primary key plus the identifier:

For example:

SiteUserTable

sitekey CHAR(16),
id AUTO INCREMENT,
firstname VARCHAR(255),
surname VARCHAR(255),
timestamp TIMESTAMP,
etc..
PRIMARY KEY sitekey, id;

Please note this is not the actual table definition - it's for
illustration only. Also, there are *many* tables using this composite key.

So basically, I end up with data like this on the centralised database:

SITE_1, 1, Fred, Flintstone
SITE_1, 2, Wilma, Flintstone
SITE_2, 1, Homer, Simpson
SITE_3, 1, Foo, Bar
SITE_1, 3, Barny, Rubble

Now, the only downside I've found with this is that because I'm using a
compound key, it makes me queries more complicated. This is fine, but
I'm wondering if I'm missing a trick.

If I use GUID's then I can just use that as the primary key, and I won't
have to constantly use the sitekey.

However, I'm then storing a much larger primary key and I've read this
can introduce performance problems amongst other things.

Does anyone have any thoughts on this? Does the way I'm doing it seem
sensible? I have a mock up being tested at a few sites and so far so
good - there's been no issue with replication so far, and I don't expect
there to be because the compound key is guaranteed to be unique because
of the sitekey (and there's only ever one database per sitekey).

PS - I would be wrong to rely on the ID being auto-incrementing wouldn't
I? My original replication algorithm worked by looking at the latest ID
I had, and then requesting all ID's greater than that. I've since
changed it to use timestamps.. Better?

Thanks,
Mark.
--

On 12/07/2010 18:44, Mark S. (UK) wrote:

Forgot to mention; the compound keys also make foreign key referencing a
pain, which is the other reason I'd like to simplify it by having
non-composite primary keys. On top of that, there is the complexity of
it being a "temporal database" - as in, it stores start_date and
end_date + all the database functions to make that work, etc.. So if I
can simplify it anywhere without causing problems, then that would be good!

Thanks,
Mark.
--

Mark S. (UK) wrote:

Ignoring all the myriad objections to spurious synthetic keys and how
they will require additional constraints to prevent otherwise
undetectable duplication/contradiction of facts, I will just point out
that UUIDs come in five "flavours". If you want to have a timestamp
element that suggests you'd want to be looking at a Version 1 (MAC
address) UUID. What kind of UUIDs does your system provide?

If you are seriously concerned about the performance implications of a
16-byte key versus a smaller one, you must have a system of
unprecedented efficiency. Normally it takes just one half-wit
programmer to use a cursor to write some row-oriented code and every
transaction it does will cost you a thousand times more than the extra
bytes of the UUID ever will.

--
Roy

Mark, I don't know that it would introduce performance problems, per
se. However, I believe that if you use GUIDs then as a consequence
different records will fall on different data pages more or less at
random. If many queries in fact work with individual "sites", then
many additional data pages may have to be accessed based on a GUID
primary key, versus the compound key, which will place data from the
same company together. Hope this make sense.