[AccessD] A Database Design Question

Oops then I expressed it incorrectly.

Each customer may design her own classifications -- the
CustomerClassifications table.
She has the opportunity to use existing classifications (thus the
Classifications table).
Any new CustomerClassification she adds is also added to the
Classifications table (so some new user can take advantage of their
existence.

Assume the Classifications table contains rows "Admin", "Staff" and
"Grunt". Our hypothetical user isn't satisfied with "Admin"; for various
reasons she needs it to read "Administration", so she adds a new
Classification (#123) and gets a new row in CustomerClassifications
(with CustomerClassificationID 345). There. I think that's a better
explanation. So the question is, should the CustomerDetails table
contain column CustomerClassificationID or ClassificationID? Either way
I get a unique reference. I'm just wondering which way is
architecturally better.

It may be worth pointing out that Customer 123's notion of Admin has
nothing to do with Customer 345's notion of Admin; it's simply a device
to let the data-entry people take advantage of existing Classifications.
No report (that I can imagine) would ever require a list of all the
Details of Classification "Admin". It's always Customer-relative --
except when we cruise Details by a column not mentioned so far -- and
that has no influence on the question.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Sunday, May 30, 2004 4:22 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A Database Design Question


Hi Arthur

It is not completely clear if you would like to store new
classifications for the current customer only or for general use so they
could be attached to other customers' CustomerDetails as well.

Also:

> When the user adds new CustomerDetails, we see
> only the Classifications of interest to said Customer (i.e. draw them 
> from CustomerClassifications and get the text value for the combo from

> Classifications).

How can you "get" text from Classifications when you are "drawing" items
from CustomerClassifications? How are these related?

/gustav


> I have 4 tables (actually lots of instances of this setup, but for 
> simplicity let's deal with one only):

> Customers -- obvious
> CustomerDetails -- many Details for each Customer Classifications -- a

> list of generic classifications CustomerClassifications -- a table 
> containing only the Classifications of interest to a given Customer

> The general idea is this. We populate Classifications with lots of 
> commonly-used items such as Admin, User, Manager, etc. We populate 
> CustomerClassifications using a combo and a NotInList event that 
> allows the addition of new Classifications that aren't already in the 
> Classifications table. When the user adds new CustomerDetails, we see 
> only the Classifications of interest to said Customer (i.e. draw them 
> from CustomerClassifications and get the text value for the combo from

> Classifications).

> Still with me? I hope so. Here's the question: should the table 
> CustomerDetails store the CustomerClassicationID or the 
> ClassificationID?

> TIA for opinions.

> Arthur

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com