[AccessD] Presenting data problem

Susan,

 What Arthur has suggested is a middle of the road approach to what is
called a Entity Attribute Value (EAV) design.   But rather than a single
table of fields that define things, typically the attributes appear as
individual records in a single table.  That allows something to have any
number of attributes.

 It's a great way to handle stuff sometimes.  A classic case is an Asset
Tracking system where you need to identify different type of things.

 Desks, Tables, and Chairs can all have an asset tag added to them, but what
about a piece of software?   All however will have common attributes because
they are "assets"; date placed in service, cost, depreciation years, date
disposed of, etc, but the details for each can differ widely. 

  So I've used a hybrid type design where I have a base table, then a table
of attributes and their values off that, and that has worked well.

  Looked like this:

tblAssets - Main table - one record for each asset
AssetID - AN - PK
Description - Text
AssetType - Text
InServiceDate - D/T
Cost - Currency

tblAttributes
AttrbiuteID - AN - PK
Name - Text
Type - Integer - (Text, Date, Currency, etc)

tblAssetAttributes
AssetAttributeID - AN - PK
AssetID - Long - CK1A - FK to tblAssets
AttributeID - Long - CK1B - FK to tblAttributes


 That type of design though only works well when the additional attributes
are simply reference data. If you have any real work to do with them, you'll
find it difficult to work with.

 There's also nothing that say you must use an attribute for something.
Likewise, nothing that says a given attribute should not be used with
something (like using a "VIN" with a desk).

  The way that's typically handled is by adding a "template" table, which is
a list of attributes that something can have.   In effect your describing a
table and if your going that far, then you might as well just use a table.

  I like Arthur's approach though; it's a neat solution to that problem.
Your still getting data type checking and with the use of queries, your
still holding your users to entering specific fields.   But you are open to
data integrity problems if you update the tables through anything other than
the queries and forms.

  Just be aware though that there is a 255 field limit in a table.  If you
have more then that, then you'll need to break things up into multiple
tables, or use a design like the one above where each attribute is a record
along with a template table.

Jim.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Tuesday, October 28, 2014 01:30 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Presenting data problem

I'm back asking for more guidance on the simple animal tracking database
that I'm working on.

The animal info is all normalized and I have input forms -- everything's
fine. Now I'm adding tables and forms for tracking these animals when they
leave us. The term they use here is disposition -- so each disposition
record will have a link to the individual animal table, as a foreign key.

Each disposition record will have a few more records, such as date. Also,
each disposition record will have a description, which I've added as a
lookup table. A few of those require no further information, but some do.

If disposition is by death, there's a whole slew of possible choices --
options and some new more information. Did we use the remains, and if so
how? Did we send the carcass for a necropsy and if so, what were the
results. If the disposition is release into the wild, they want to track
where. If the disposition is relocation, they want to track the
institution. If sold, they want to track buyer and price.

To store all this additional information in the disposition table seriously
breaks normalization rules. I'm wondering about dumping everything beyond
the disposition table into a table -- I don't care about the blanks and
everything will link back to the disposition records -- all as one to many.
It seems like a reasonable plan, but I'd like opinions. Right now,
everything's normalized in its own option table, but that just seems
unnecessary.

I've been sitting on this for about a week. The initial disposition form is
no problem. I'm struggling with how to efficiently manage all these
possibilities. Subforms seemed like a good idea -- enabling them depending
on the disposition type, but geez... that's a lot of subforms. Option
controls will work in some cases too.

I don't need to display all the possible options on the same form at one
time. I'll only those that are required by each option. Additional forms --
popup style -- would certainly work, but in some cases, the popups will
have popups, which have popups. :) All of these will link back to the
original disposition record.

Thoughts?

Susan H.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com