Access tables and subclassing

[Rene]

Hello!

My questions relate to subclassing, within the context of table design.

I have a back-end database containing a 'Customer' table. Three distinct
front-ends interface with the data, each with its own requirements with
respect to 'Customers'.

In a recent spurt of normalization, and since often, several fields in the
customer table do not contain any data (ie. not known, not relevant), I am
taking a serious look at the concept of 'subclassing' with the 'Customer'
table.

Hoping not to violate any rules here, you can take a peek at the
relationships diagram for the Customer table by directing your browser here:
http://www3.sympatico.ca/rene_presse/subclassing.htm

From the diagam, I infer that:
- all customers (ie. jumpers) have a name and Date Of Birth
- some have coordinates, address, city, ...
- for some we have financial (credit card, bank) information
- some of them have their own rig
- finally, some of them may be 'grounded' (medical, drugs, debt...)

My questions:
Is subclassing sound in this context? Or is it overdone?
What are mistakes/pitfalls to avoid in subclassing?
What is recommended reading on the topic of MSAccess & subclassing tables?

Thanx a million

René
Montreal

 

[Steve Schapel]

René,

As far as I know, it is pretty much a judgement call when to the take
the step into a sub-classing or sub-typing design. I would personally
only consider it if there are certain data which only relates to a
very distinct and separate group within the main data set. And
particularly if there is more than one grouping, and each grouping has
its own distinct data requirements. I would not usually regard blank
spaces in some of fields as being sufficient reason in itself. On the
basis of your diagram and what you have told us, I don't think your
idea is "wrong", but I would leave it all in the one table myself.

- Steve Schapel, Microsoft Access MVP

 

[Tim Ferguson]

Is subclassing sound in this context? Or is it overdone?
What are mistakes/pitfalls to avoid in subclassing?
What is recommended reading on the topic of MSAccess & subclassing
tables?

I agree with Steve: there are no hard-and-fast rules about when subclassing
becomes useful. As with all things: it's a tradeoff between making the GUI
a little more complex versus some queries being a bit easier and quicker.

Sometimes there are special reasons: for example, in a secured database
it's a bit easier to have financial or personal details in separate tables
in order to restict access separately from the more general stuff, than to
mess about with OWNERACCESS queries.

Another reason is where you know you have queries on just the subclasses:
analysis of RigWeights or Duedates, for example, would not need any
reference to the Customers table at all.

So, nothing wrong then, but about as many different opinions as you'll get
respondents. Rebecca Riordan on these NGs is probably the queen of
subclassing!

All the best


Tim F