Re: Design question

Mark S. (UK) wrote:

> Mark S. (UK) wrote:
> 

> 
> 
> 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.

Mark,

I don't think theory-based answers to your questions exist, and I also find design via usenet counterproductive at best.

You are struggling with a number of issues including distribution and partial fault tolerance. When needed, these are very important considerations.

As a general principle, it is best to handle your distribution and fault tolerance needs at the physical level of discourse without exposing any of that in your logical data model. You are trying to handle it entirely at the logical level, which I think is probably inappropriate.

Pragmatically, it is best to consider these issues at the earliest stages and to use your distribution and fault tolerance requirements as a basis for comparing dbms products and then choosing the dbms product that best addresses your distribution needs. Once you have chosen a dbms product, which it sounds like you have already done, then it is best to consider the specific features that dbms product has for handling distribution, replication, fault tolerance et cetera physically with the least impact to your logical design.

Many products now have features for distribution or replication, for assigning globally unique numeric identifiers, for reconciling off-line changes and more. Perhaps even the product you use has such features.

The issue you have with user id's is a basic requirements and design issue. Assuming you are using user id in the conventional way, the purpose of a user id is to uniquely identify users of a system. Pragmatically, the scope of such identity has to be an entire independent system. Generally, that means globally throughout a system. In your case, that may not be true. Individual sites, though, are not independent systems because they are tied together into enterprises. It may be your enterprises are entirely independent from one another. Or it may be they have interdependencies too.

Sometimes, it makes sense to scope user ids to have partial independence, which is what email addresses do. The entire globally unique id is the full email address with a user name, the _at_ symbol, and a domain, while the locally unique id is simply the user name. Doing so simplifies some things while complicating others.

In any case, I think you need to decide the scope of your user ids based on what users will expect and what meets the needs of the system you are designing. You may have to trade off conflicts between them.

In the end, at this point in time, design is more art than science. Received on Sat Jan 24 2009 - 19:14:38 CET