Best practice

[Fay Yocum]

I am getting started with a project and I want to make sure I am doing it
using "best practice" techniques.

Situation: I will be having multiple tracks in the db. For example CE
providers, Certification authorities, and the individual, there are more but
I will stop here. Each of those groups will have addresses. I want to have
but one address table and using one basic subform on different main forms.
This keeps my address forms to a minimum and allows me display all addresses
in a datasheet.

Question: What is the best way to do this? What I am currently doing is the
tblAddresses has the PersonID, CEProvID, and CertAuthID fields, plus others
.. When binding the subfrm to the CE Provider main form I use both the
PersonID and CEProvID. This appears to work correctly. But is this the best
way of dealing with the project?

Thank you for your thoughts.

Fay

 

[Arvin Meyer]

Hi Fay,

Let me restate what I think your question is:

You have multiple entities with some of the same characteristics. One of
these characteristics is 1 or more addresses.

Even though the addresses have the same fields, there is a difference in
that they are not primary entities, but in fact a descriptor entity (they
are the address property of the main entity). I usually handle these
differently, but they can be handled the same way. The deciding factor is
what the actual data is and how it will be used.

1. You can put the company address (assuming there is only one) in the
company table. and put the people addresses (assuming multiple addresses) in
a separate table. If a company has branch offices add a column in the
company table called ParentCompanyID and put the parent company's ID in it.
After all, a company can really only be in a single place at a time. This
assumes that the core data fields that describe the company are few.

2. If there are many fields that are unique to the company, but not to the
branches. It will be better to store the branch addresses in an address
table. Since addresses have pretty much all the same fields, all that's
needed is to add a field to point back to the main entity so that the
foreign key for the Person, or Company will be identified as to where it
originates. This is the most "relational" but can lead to other problems if
queries are not carefully written.

3. Lastly, you can use multiple address tables. This, while not as strictly
conforming to Normalization rules, is certainly the most flexible, because
you can change it to either of the other 2 methods with ease.

There are good database designers who would put all the primary entities in
one table, and that simplifies everything on the address end of it, but
makes it more complex on the main entity. (i.e. Does the company name field
go in the FirstName or LastName field or visa-versa?) I personally prefer to
remain as flexible as I can because I see the business practices of every
company I deal with change frequently. I haven't had the need yet to go with
the 3rd choice and usually elect to go with the first. I have a database
project coming up though, where the 3rd choice may be the most intelligent
way to go.

Bottom line: Best practices are what you think they should be, given a
thorough analysis of the project. The rules are written on paper, not carved
in stone.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Fay Yocum]

Thanks Arvin. Your thoughts are helpful. I think the critical point for me
is your last paragraph. I have often wondered if I was doing things wrong
and not using best practice techniques. Fay