Nigel,




I have been using a "placeId" as a part of a combo-pk for some time
and I like it a lot. I have it implemented for clients having more
than 10 diferent places (city, states, offices) and it works
perfectly. (I use smallints for the siteid, btw. My clients don't have
so many sites. Int64 would be rather big.)




Although Sean's method would work also, one big plus using a siteid is
that you know where the data came from looking at the primary key.




Andrew

> To allow future replication of a schema to multiple sites,

preventing

> collisions of primary keys, is it a good idea to combo-primary key


> generator-based id's on tables with a siteid?


>


> For example:Buildings on a site:


>


> create table tbl_sitebuildings(


> int_building INT64 NOT NULL,


> str_name VARCHAR(100) NOT NULL,


> PRIMARY KEY(int_building)


> );


>


> This would be fine for a single site, but if it ever needed

replication,

> there'd be heartache everywhere, as each site would like a building

number

> '1'...


> yes/no?


>


> However:


> create table tbl_sitebuildings (


> int_siteid INT64 NOT NULL,


> int_building INT64 NOT NULL,


> str_name VARCHAR(100) NOT NULL,


> PRIMARY KEY(int_siteid, int_building)


> );


> Allows a generator to supply int_building values, which start from

1, inc by

> 1, and can collide as much as they like. Adding in sites involves

using a

> default siteid for the new site's data.


>


> Yep? Nope?


> (My schema is in early days yet. Perfect time to plan for expansion)


>


>


> Nige.