[AccessD] A Database Design Question

Hi Arthur

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

So you will be running a denormalized schema? Or did I miss something?

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

As you will copy any entry in CustomerClassifications to
Classifications as well, I would refer solely to Classifications. To
have mixed references (some to Classifications, some to
CustomerClassifications) would not be an easy task if you (as I
expect) will enforce referential integrity too.

/gustav

> 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