Subclassing entities

[Pieter Linden]

Hi,

This question refers sort of to Rebecca Riordan's article on Access
web about subclassing entities:

http://www.mvps.org/access/tables/tbl0013.htm

How practical is this? I am writing a database to track Computer
equipment/inventory as well as other devices. Computers, of course,
have software installed, so I want to keep track of that. I was
thinking I could basically follow Rebecca's example and have a
Super/sub-type setup, where "InstalledSoftw are" would be related to
the subtype of equipment "computer". Does this basically mean that I
have to have a subform for every subtype? (Uhh.. yeah... how did you
want to write the data to the underlying table?!) The problem is that
so many different items are loaned to people that nothing else will
work - keys, cell phones, laptops, printers, desktops, monitors,
pagers...

Are there any gotchas I have to look out for?

Thanks,

Pieter

[Larry Linson]

Re: Subclassing entities

"Pieter Linden" wrote
[color=blue]
> This question refers sort of to Rebecca
> Riordan's article on Access web about
> subclassing entities:
>
> http://www.mvps.org/access/tables/tbl0013.htm
>
> How practical is this? I am writing a database to track Computer
> equipment/inventory as well as other devices. Computers, of course,
> have software installed, so I want to keep track of that. I was
> thinking I could basically follow Rebecca's example and have a
> Super/sub-type setup, where "InstalledSoftw are" would be related to
> the subtype of equipment "computer". Does this basically mean that I
> have to have a subform for every subtype?[/color]

You would have to have a separate subsidiary Table for each type _that has
differing information_... for any types that have identical information, the
Records can be in a combined subsidiary Table. If, for each type, you keep
the same information... say just serial number, manufacturer, date of
aquisition, and date removed from service... they could all be kept in one
Table. However, if you keep dimensions like screen size for displays, and
transmission speed for modems, and speed for CPUs and memory, then those
would require separate Tables. This seemed so obvious to me, but perhaps it
is not, because it took a little thought for me to write it down.

Obviously, if you have different tables, then you'd need a different form
embedded in the subform control -- that can be done by changing the Source
Object of the Subform Control, or creating Several Subform Controls each
with different Source Objects whose visible property you switch on and off,
as described in Rebecca's article. The first approach is likely to be more
efficient in using your computer resources, but which is more responsive,
only testing would tell.

Larry Linson
Microsoft Access MVP

[HSalim]

Re: Subclassing entities

One of the problems with subclassing entities is deciding how and where to
split.
Let us continue further with Rebecca's discussion, where Customers are
represented as parties which can then consist of
individuals and companies. But the discussion does not have to stop with
customers. What about vendors? Employees?
You create a subclass for each of those types under parties.

What about Owners/Shareholders? There are so few of them (especially for
privately owned businesses) and the number of transactions are so few, that
you begin to question if you should add these as vendors, flagging IsOwner =
True

Now consider the original poster's case - when you have inventory items:
Items can have an infinite number of variations. In the poster's case. The
list seems endless.keys, cell phones, laptops, printers, desktops, monitors,
pagers...
and I'll add one more just to make things worse: Uniforms. - now you are
dealing with an entirely different type of item
and the none of the attributes are shared with the hardware types. (ah, you
say, but neither does a monitor share anything
with a computer)

How do you handle something like that while making sure you maintain
flexibility (i.e. without requiring modifications to the software)
while retaining the ability to add a new class of items, and enforcing a
good amount of data integrity?

There are no easy answers. I have been toying with an idea for this but
have never implemented as yet. I can think of some drawbacks, all of which
are easily overcome. However, others may be able to shed more light on the
drawbacks to this that I have never thought of. So here it is, represented
as tables:
ItemMaster: ItemID, ClassID, Alias, Weight, Dimensions....
ItemClasses: ClassID, Description, ...
ClassAttributes : ClassID, AttribID, Description, DataType, Required,
DefaultValue, Validation Rule, EnumID
ItemAttributes: ItemID, AttribID, AttribValue
Enumerations: EnumID, Description, Value

To expand on this:
ItemMaster: ItemID, ClassID, Alias, Weight, Dimensions....
Primary table, contains attributes common to most items. Some attributes
may remain unused - such as Weight will be unused for a service item such as
consulting hours.

ItemClasses: ClassID, Description, ...
Simple table listing class level commonality. This could include attributes
such as Costing Method ( FIFO/LIFO...None)

ClassAttributes : ClassID, AttribID, Description, DataType, Required,
DefaultValue, Validation Rule, EnumID
Lists each of the attributes that must be captured for each item. For
Example
DesktopComputer would require CPU, Memory, HDD, Manufacturer, BusSpeed, ...
Laptops would require all of Desktop, plus Screen Size and type
Monitors would require Screen Size, Manufacturer, and EnumID = 1
Network Cards would require MAC address among other things
You would use Validation Rule or Enumeration, but not both

Enumerations: EnumID, Description, Value
Would list acceptable list of values for items that allow only an acceptable
range of values: For example
Monitors would allow require an enumeration of LCD, Plasma or CRT_Mono(if
you still have any), CRT_Color

ItemAttributes: ItemID, AttribID, AttribValue
Here is where you specify attributes for each item -
Notebook, SerialNumber, a1s2d3f4g56
Notebook, Manufacturer, IBM
Notebook, Model, ThinkPad T30
....
One advantage to this approach: You can use a generic subform to capture
Item Attributes. I need to work out exactly how to do this. It would have
been ideal to have a grid control, like VSFlexGrid where some columns can be
ReadOnly.
In any case, some fancy coding will be needed to implement the validation
rules, (which would need to be implemented even of you were to use dedicated
subforms fore each sub-class)

Now we come to what perhaps is the most complicated portion of this
implementation. - Transactions.
The original poster has a much simpler transaction - each item, once added
to the ItemMaster and subclasses becomes an item in its own right, and is
"loaned" or "Issued" to an employee. Tracking that is simple
EmployeeID, itemID, IssueDate, ReturnDate would suffice

What to do about trading in these Items? What happens if you were to buy
say, 1000 thinkpads for resale? I still need to think this portion through,
and one way to do that is to start using an ER tool to model this.

Many software packages make a clear separation between
customers/vendors/employees, completely ignoring real-life scenarios where
one individual could be all three, and in many cases, especially in trading
companies, customers and vendors often overlap.
One of the only accounting packages that does not separate
customers/employees and vendors is Quickbooks - right out of the box, it
uses one ID for all parties. Unfortunately, I have never liked QB too much
for other reasons, but this was one feature that I wished the bigger rivals
would match.


Regards
HS





"Larry Linson" <bouncer@localh ost.net> wrote in message
news:e37db.1661 6$ZR1.13968@nwr ddc01.gnilink.n et...[color=blue]
> "Pieter Linden" wrote
>[color=green]
> > This question refers sort of to Rebecca
> > Riordan's article on Access web about
> > subclassing entities:
> >
> > http://www.mvps.org/access/tables/tbl0013.htm
> >
> > How practical is this? I am writing a database to track Computer
> > equipment/inventory as well as other devices. Computers, of course,
> > have software installed, so I want to keep track of that. I was
> > thinking I could basically follow Rebecca's example and have a
> > Super/sub-type setup, where "InstalledSoftw are" would be related to
> > the subtype of equipment "computer". Does this basically mean that I
> > have to have a subform for every subtype?[/color]
>
> You would have to have a separate subsidiary Table for each type _that has
> differing information_... for any types that have identical information,[/color]
the[color=blue]
> Records can be in a combined subsidiary Table. If, for each type, you keep
> the same information... say just serial number, manufacturer, date of
> aquisition, and date removed from service... they could all be kept in one
> Table. However, if you keep dimensions like screen size for displays, and
> transmission speed for modems, and speed for CPUs and memory, then those
> would require separate Tables. This seemed so obvious to me, but perhaps[/color]
it[color=blue]
> is not, because it took a little thought for me to write it down.
>
> Obviously, if you have different tables, then you'd need a different form
> embedded in the subform control -- that can be done by changing the Source
> Object of the Subform Control, or creating Several Subform Controls each
> with different Source Objects whose visible property you switch on and[/color]
off,[color=blue]
> as described in Rebecca's article. The first approach is likely to be more
> efficient in using your computer resources, but which is more responsive,
> only testing would tell.
>
> Larry Linson
> Microsoft Access MVP
>
>[/color]

[David W. Fenton]

Re: Subclassing entities

HSalim@msn.com (HSalim) wrote in
<YUjdb.8421$541 .3682@nwrdny02. gnilink.net>:
[color=blue]
>Many software packages make a clear separation between
>customers/vendors/employees, completely ignoring real-life
>scenarios where one individual could be all three, and in many
>cases, especially in trading companies, customers and vendors
>often overlap. One of the only accounting packages that does not
>separate customers/employees and vendors is Quickbooks - right out
>of the box, it uses one ID for all parties. Unfortunately, I have
>never liked QB too much for other reasons, but this was one
>feature that I wished the bigger rivals would match.[/color]

I liked your ideas in the discussion I did not quote -- I have no
additional comments, but wanted to say that I was definitely
thinking along the same lines as you were for cases where the
attributes for the many items are widely variable.

I don't think, though, that people vary much at all.

For a customer/employee/vendor, the attributes are mostly the same
-- you have names, addresses, phones, email, birthdates, etc. A
small number of things apply to people and not to companies, but
for the most part, there's not much difference. The main question
is whether you put companies in a separate table.

I tend to have tblPerson and tblCompany. Vendors would be
companies. The person table would have contacts at a particular
company.

The problem there is that companies have addresses and phone
numbers and so do people.

So, the solution would be to use self-joins on a table that
included both people and companies. Then all you have to handle is
the names: store the company name in an Organization field or put
it in LastName? If you put it in a different field than the field
used for people then you'll need to calculate the authoritative
entity name for display and printing. But this is something you
could hand off to a UDF or put in a query that becomes your base
for all recordsets.

In any event, I think people/vendors are far simply than inventory.

I've only done one major inventory application, and that was for an
antiquarian music dealer (http://wurlitzerbruck.com/). I was
converting from an old dBase III app that was written by a former
COBOL programmer, and he used a bunch of re-usable fields and a
record type (there were about a dozen different kinds of inventory
items, all wildly different in their attributes). This seemed like
a really good idea at the time, but the implementation I chose was
a very poor one: I copied over dynamically changing the "screen"
from the old program. That's a simple thing in dBase, since you're
character-based, but in Access, it wasn't simple at all. I did it
by hiding/showing controls on a tab control page, and moving them
around, changing the control sources and changing the labels. It
resulted in completely unmanageable code that I dread revisiting.

In retrospect, what I *could* have done that would have worked
better was to create a tab page for each inventory type, then show
only the tab page for the current record's inventory type. This
would require a few hoops to jump through for setting focus, but I
had that problem with the solution I chose.

In any event, a solution like you have proposed would have been far
more manageable. However, I do think that the UI would have been a
big challenge.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[HSalim]

Re: Subclassing entities

My feeling is that the UI would be simpler than you think if the application
is done right.
The bulk of the work will be in designing the system first - the database
design if you will, but also broadly how the application would work.
The ability to use forms/subforms and even synchronized subforms is quite
powerful and flexible and
with some imagination, can be used very well. One of the the greatest
features of access is the ability to use subforms and in the consistency
they provide.

In some cases, it makes sense to hide and show controls - for example, in a
survey of multiple choice questions, the textbox for other becomes visible
and required when and only if Other is selected as the choice.
But I am not in favor of overlaying controls and dynamically moving them
around, and or showing/hiding them. The tab control, as you mentioned can
be used for that purpose, and simplifies a lot of the coding.

While people and companies are not inherently different in the way a
transaction flows,
the devil is in the details. IF you look at the structure of many databases
out there, you'll see
that there is a tremendous amount of detail that is captured at the Master
record level.
For example, Vendors: Tax Registration#, 1099 type...
Customers: Credit Limit, Hold, MaxWrite-off Amount...
I would not have a separate table for companies.
About the only time the individual's details matter is if he/she becomes an
employee.
Otherwise, the vendor's name goes into company name and into Primary contact
I don't like your idea of calculating the authoritative name, but it could
be worth it.

Addresses should always be stored in an addresses table with a reference to
the parent.

HS

[Pieter Linden]

Re: Subclassing entities

Thanks, Larry. That confirmed my suspicions... only problem with it
is that whoever is that the interface has to be changed every time
someone creates a new subtype, but nothings perfect I guess.