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" <bo*****@localhost.net> wrote in message
news:e3*******************@nwrddc01.gnilink.net...
"Pieter Linden" wrote
> 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 "InstalledSoftware" would be related to
the subtype of equipment "computer". Does this basically mean that I
have to have a subform for every subtype?
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