Re: organizing tables?

"Bob Badour" <bbadour_at_golden.net> wrote in message news:otKQa.829$4P6.136561801_at_mantis.golden.net...
> > Indeed. Why 'application' and 'user' names. Are these important?
>
> Yes, they are important. Role and application version are as well, but I
> tend to think of versions as identifying different applications.
>
>
> > Why one
> > hierarchy, why not many. Why a hierarchy at all. All good questions.
> > To tackle them logically, we would propably want some agreed logical model
> > of 'users', and maybe of 'applications'.
>
> I suggest using one or more candidate keys for namespaces where at least one
> candidate key is compound. One can drill down a compound candidate key in
> any order if one wants to pretend the namespace has a hierarchic order.

Not sure I understand.
Do you mean for example including COMPANY_ID as a column in the compound candiate key for every entity in the part of the database to be split by company??

> For the most part, I agree that users will generally want to temporally
> separate their interactions with different databases. For the most part, a
> user accessing the marketing database wants to have confidence that nothing
> they do will directly change the accounting database. However, some users
> may have access to both marketing and accounting databases with each of
> these databases defining only a subset of the users' view. And even users
> who generally want to access one and only one database may at times want to
> access both simultaneously (albeit very carefully).

At some level it is just a matter of perception. But I would want a DBMS to encourage (and allow) users to see the complete set of relvars that they can see at any moment as a single database. If they can see both marketing and accounting relvars, they should just see that as on (integrated) database. In particular everything about such an integrated database should work exactly as a 'single' database works.

> Both marketing databases and accounting databases may have Customer
> relations where there are subtle differences between them. Further, the
> marketing database may have multiple Customer relations where a Customer
> means one thing to a PromotionPlanning application and something subtly
> different to the CRM or CallCenter application.

Tell me about it. All nice messy data integration stuff. If two relvars are both called Customer (or, alternatively, have exactly the same tuple type), then the integration layer must make them the same relation as far as users are concerned. If the layer cannot do that because they don't (or should not) contain the same data, then the layer needs to rename at least one of them. Marketing_Customers & Account_Customers say.

> The user name does define a subset of the database through the security
> function. I suggest that users do think in terms of separate databases like
> the marketing database and the accounting database, and that databases
> define subsets of the user's view.

I suggest users only think in such terms because that is the way current systems work. It is not essential. Another way would be for users to see the accounting part of the corporate database, or the marketing part and to see that these are just some subsets of a single whole.

> > what are we really talking about here. Naming scope?
[snip]
> The question then becomes: What defines context? I can make plenty of
> suggestions: database, application, version, role, user, schema, relvar.

I guess we should define contexts independently, then link them to users, schemas, roles, versions, applications as required.

A context is then just a named database subset (I suggest).

And a database subset is any subset of database relvars and tuples that is itself a valid database.
E.g.

1. all constraints only reference relvars within the database subset
2. everything that is not constrained can be updated (i.e. no hidden constraints)
3. everything that is constrained cannot change value (i.e. no hidden updates).

> > If it is to do with naming scope, then schema design and evolution are
> some
> > other things that play a part here..
>
> I suppose one could propose support for versioned database views, versioned
> application views, versioned role views, versioned user views and versioned
> schema views, but I tend to think of versions applying mostly to
> applications.

I was thinking more of what happens to canned statements if the scope changes in such a way as the attributes the statement ultimately reference change even if the canned statement itself has not. Does it reference the original attribute or the one according to the new scope?

> Alas, upgrading users is easier said than done.
>

:-)

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Tue Jul 15 2003 - 12:36:05 CEST