473,320 Members | 1,939 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Subclassing entities

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 "InstalledSoftware" 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
Nov 12 '05 #1
5 6461
"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
Nov 12 '05 #2
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

Nov 12 '05 #3
HS****@msn.com (HSalim) wrote in
<YU*****************@nwrdny02.gnilink.net>:
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.


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
Nov 12 '05 #4
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
Nov 12 '05 #5
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.
Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: BJörn Lindqvist | last post by:
A problem I have occured recently is that I want to subclass builtin types. Especially subclassing list is very troublesome to me. But I can't find the right syntax to use. Take for example this...
11
by: Brent | last post by:
I'd like to subclass the built-in str type. For example: -- class MyString(str): def __init__(self, txt, data): super(MyString,self).__init__(txt) self.data = data
3
by: Kunle Odutola | last post by:
I have a database that tracks players for children's sports clubs. I have included representative DDL for this database at the end of this post. A single instance of this database supports...
3
by: Michel de Becdelièvre | last post by:
I have some *performance* trouble reading MathML files in my application (in ASP.Net). - I have small MathML files (2-3k) as input - as (almost) all MathML files these use entities. I have no...
2
by: Frantic | last post by:
I'm working on a list of japaneese entities that contain the entity, the unicode hexadecimal code and the xml/sgml entity used for that entity. A unicode document is read into the program, then the...
16
by: manatlan | last post by:
I've got an instance of a class, ex : b=gtk.Button() I'd like to add methods and attributes to my instance "b". I know it's possible by hacking "b" with setattr() methods. But i'd like to do...
6
by: clintonG | last post by:
Can anybody make sense of this crazy and inconsistent results? // IE7 Feed Reading View disabled displays this raw XML <?xml version="1.0" encoding="utf-8" ?> <!-- AT&T HTML entities & XML...
5
by: Ray | last post by:
Hi all, I am thinking of subclassing the standard string class so I can do something like: mystring str; .... str.toLower (); A quick search on this newsgroup has found messages by others
7
by: tempest | last post by:
Hi all. This is a rather long posting but I have some questions concerning the usage of character entities in XML documents and PCI security compliance. The company I work for is using a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.