[AccessD] A Database Design Question

We have a database (canned) in use with 'seed' entries for lists and
combo-boxes etc.  Nearly all of them allow new entries from the user.
The annoying part is that the 'seed' entries are fixed and are showing
up in lists where we don't use them, i.e.. the fixed/original entries
cannot be deleted.

How many of these 'classifications' do you expect - if there are only a
few as you've indicated then I would put them in the same collection
with a gracious attitude of 'not material'.

One of the most annoying things for reporting is when the database
design exceeds functionality, and believe me, the aforementioned canned
database is a case study in bad-design and relationships dependant on
other relationships.

> -----Original Message-----
> From: Arthur Fuller [mailto:artful at rogers.com] 
> Sent: Sunday, May 30, 2004 6:29 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [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
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>