Re: Design question

Mark S. (UK) wrote:
> Hi,
> I'm currently working on a system that consists of several sites. Each
> site runs a system with its own local database, and then periodically
> each site updates the "master" site with its data, allow the data to be
> viewed centrally.
>
> It must be done like this because each site may not always be on the
> network, but the data must always be available from the central server.
>
> To enable the master database to contain all the site database
> information, I use a composite key, with the first part being the site
> identification, and the second part being the row identification. This
> allows user with row id 5 for instance to exist more than once because
> paired with the site id it is still unique.
>
> On top of this, there is another layer. Each site must be contained
> within an "Enterprise", which is just another identifier. So, I have a
> table called enterprise which contains the list of site ids.
>
> So far then, I have this (obviously cut down to only show the relevant
> columns) : -
>
> table: enterprise
> column: id
>
> table: site
> column: id
>
> table: user
> column: site_id
> column: id
> column: label
> etc..
>
> With the site_id and id making the composite key.
>
> Now, I've just had to add the concept of "enterprise" level users.
> Initially the only clean way I could think of doing this is having two
> separate user tables; enterprise_user and site_user : -
>
> table: enterprise_user
> column[key]: enterprise_id
> column[key]: id
> column: label
> etc..
>
> table: site_user
> column[key]: site_id
> column[key]: id
> column: label
> etc..
>
> However this does not seem very "normalized"..
>
> Other ways I considered are : -
>
> table: user
> column[key]: id
> column[key]: enterprise_id
> column: site_id
> column: label
> etc..
>
> But this does not make sense as an enterprise user is not associated
> with any site. I could have a dummy site, but I really do not like
> that. I could just set site_id to NULL, but I'm not a big fan of NULL
> in database and it just seems a bit hacky.
>
> So then I thought of adding a link table:
>
> table: user
> column[key]: id
> column[key]: enterprise_id
> etc..
>
> table: user_site <== link table
> column: enterprise_id
> column: user_id
> column: site_id
> etc..
>
> with a unique constraint on enterprise_id, user_id (the composite key
> that identifies a user in an enterprise).
>
> I think that also works.. basically with no entry in the "user_site"
> table, they are enterprise users. An entry in user_site effectively
> limits them to being a site user.
>
> However.. this might create complications in the "master" update process.
>
> Part of the batch update process involves updating user information.
> Enterprise users (only editable at the master) must be propagated down
> to each site, so that the site is "aware" of enterprise users.
>
> Site users (only editable at the site) must be propagated up to the
> master, purely so all the (read only) data at the master is complete.
>
> So I'm wondering if having the two tables separate will be just much
> simpler (although not being terribly efficient in terms of pretty much
> repeating a table structure).
>
> I hope all that makes sense!
>
> Thanks,
>
> Mark.

After some further thought, I've realised there is another problem: user   name (or label as I called it above) uniqueness.

A user name at a site may be re-used, as the site is pretty much a self contained system. So a user "fred" can exist at site 1, and at site 2.   Enterprise users however must remain unique.

So.. site users are unique to sites, and enterprise users are unique to the enterprise (which is the "container" for sites).

This brings up a problem with the normalized design:

table: user
column[key]: enterprise_id
column[key]: id
column: label

add constraint unique on user(enterprise_id, label)

table: user_site <== link table

column: enterprise_id
column: user_id
column: site_id

This is fine for keeping users unique at the enterprise level, but doesn't work out for site users when they've been propagated back to the "master" database.

So, currently I'm thinking two separate tables. I'm not repeating data, I'm just repeating some elements of a table, so perhaps it isn't that bad?

Having read these posts back, I'm not sure I've explained the scenario very clearly. I hope it makes some sense at least.

Mark. Received on Sat Jan 24 2009 - 18:03:52 CET