[AccessD] Names in tables - best practices

Just a thought Rocky, but you could get away without redesigning
anything but the tables themselves.

One of the handy features of Access is that tables and queries are
functionally identical to the things that use them.

So, if you rearranged the table structure, you can actually make queries
named with the original table names, that display the data the old
tables actually showed, and the rest of the system doesn't know the
difference.

For example, let's say you had tblEmployees and tblCustomers

tblEmployees:

FirstName
LastName
EmployeeNumber
Etc.

tblCustomers:

FirstName
LastName
CustomerNumber

If you join this data into tblPeople

FirstName
LastName
IDNumber
Employee (yes/no)
Customer (yes/no) (this is just an example)

If you then had this saved as 'tblEmployees'

SELECT FirstName, LastName, IDNumber AS EmployeeNumber FROM tblPeople
WHERE Employee=True

And this saved as 'tblCustomers'

SELECT FirstName, LastName, IDNumber AS EmployeeNumber FROM tblPeople
WHERE Customer=True

Even though your data structure would be totally different to you, the
system would use the queries just like they used the tables.

Granted, this is a band-aid approach, because a major structure change
should have the system converted to use the advantages of the new
structure, but as you can see, this would allow for future changes to
utilize the new structure, and let's the current stuff keep chugging
along.

Drew




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
rockysmolin at bchacc.com
Sent: Monday, August 20, 2007 2:05 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Names in tables - best practices

Thanks for that, Charlotte.  I thought it would be better not tocombine
them at this point, but I see what you're saying and the bet is that
upfront cost of redesigning the db wil probably be offset by simplicity
of
maintenance?

Original Message:
-----------------
From: Charlotte Foust cfoust at infostatsystems.com
Date: Mon, 20 Aug 2007 08:22:46 -0700
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Names in tables - best practices


Definitely combine the tables.  Maintenance is much simpler.  If some of
the data collected is radically different for different roles, put the
common fields in the persons table and use one-to-one joins to tables
containing the unique data collected for that particular role.

Charlotte Foust


The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI BusinessSensitve material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.