Re: UUID vs Compound Key

On 12/07/2010 18:44, Mark S. (UK) wrote:
> 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.
> --

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.

--