? Plan For "Unified" Home Inventory/Tracking Database

I want to created a "unified" home Inventory/Tracking system using Access
2003, and I wonder if anyone has any comments on my idea, and if it can be
done without breaking all the design rules.

I want to make a system that has a common front end regardless of if the
item is a CD, DVD, tool, slides in a slide tray, files stored on my computer,
etc. These items are all similar in that they have a name(CD title), they
have a physical characteristic(LP record), someone made it(actor/artist),
they are kept somewhere(in a slide tray, in my Zio Tek Media Carousels), and
they might have subparts(track name). The basic difference is the headings
that would appear on queries and reports.

My initial thought is to create a "base" table that contains the intrinsic
name of the item (primary key), basic common information columns, and
column(s) containing indicators (you can tell I was a programmer) describing
the basic phyical characteristic(s) if the item, such as if it is a CD or
power tool, where it is physically located, etc. This table would then be
linked to a table (and possibly subtables) which would contain enough
columns to describe the "details" of the item. For some items, some columns
or subtables wouldn't be used.

This way I could create more generic queries and reports by using VBA and
the values of the indicator columns to dynamically change the headings, etc.
to match the appropriate item. Some storage might be wasted for columns that
are not used by some items, but I would rather have a unified system than one
that uses the least disk storage.

Any basic thoughts on my plan?

Lastly, I just read "Microsoft Office Access 2003 Inside Out" by John L.
Viescas, and I'm wondering why I would even consider making a "local" .mdf
database instead of the "upgradable" .adp project type. I have MSDE running
on my system, and the only reason I can possibly think for using .mdf
databases is for possible performance issues. Am I missing something here?

--
Carl

"BitPusher" wrote in message
news
I want to created a "unified" home Inventory/Tracking system using Access
2003, and I wonder if anyone has any comments on my idea, and if it can be
done without breaking all the design rules.

I want to make a system that has a common front end regardless of if the
item is a CD, DVD, tool, slides in a slide tray, files stored on my
computer,
etc. These items are all similar in that they have a name(CD title), they
have a physical characteristic(LP record), someone made it(actor/artist),
they are kept somewhere(in a slide tray, in my Zio Tek Media Carousels),
and
they might have subparts(track name). The basic difference is the
headings
that would appear on queries and reports.

My initial thought is to create a "base" table that contains the
intrinsic
name of the item (primary key), basic common information columns, and
column(s) containing indicators (you can tell I was a programmer)
describing
the basic phyical characteristic(s) if the item, such as if it is a CD or
power tool, where it is physically located, etc. This table would then be
linked to a table (and possibly subtables) which would contain enough
columns to describe the "details" of the item. For some items, some
columns
or subtables wouldn't be used.

This way I could create more generic queries and reports by using VBA and
the values of the indicator columns to dynamically change the headings,
etc.
to match the appropriate item. Some storage might be wasted for columns
that
are not used by some items, but I would rather have a unified system than
one
that uses the least disk storage.

Whenever you have 1...n things that you want to show dynamically on a form
or a report, you are talking rows, not columns. So even though the 1-n
things are bits of data about various things in your database, you need to
find a way to make those things rows.

Probably the easiest would be something like this:

ThingTypes (Defines types of things that could be described in your db)
ThingTypeId
ThingTypeDesc

ThingTypeProperties (Lists properties a given thing type could have)
PropertyID
ThingTypeId
PropertyDesc
PropertyDatatype
PropertyFieldlength

Things (things that are actually being described)
ThingID
ThingDesc
ThingTypeID

ThingProperties (actual properties of a given thing)
ThingID
PropertyID
PropertyValue

The issue with this is that Access forms don't lend themselves to this type
of structure, so you'll need to use a Left join of ThingType with
ThingtypeProperties to allow you to "pull" a list of properties to fill out
about a particular thing of that type.

HTH;

Amy

Thanks Amy for the feedback.

I'm now changing my original idea to incorporate your suggestions. First I'm
going to set up something very basic and simple to make sure I can get it to
work using MSDE. I don't have much documentation for SQL Server, so I might
have to head to my local Border's or Barnes and Noble to get some books about
it and its management.

Thanks again,

--
Carl


"Amy Blankenship" wrote:


"BitPusher" wrote in message
news
I want to created a "unified" home Inventory/Tracking system using Access
2003, and I wonder if anyone has any comments on my idea, and if it can be
done without breaking all the design rules.

I want to make a system that has a common front end regardless of if the
item is a CD, DVD, tool, slides in a slide tray, files stored on my
computer,
etc. These items are all similar in that they have a name(CD title), they
have a physical characteristic(LP record), someone made it(actor/artist),
they are kept somewhere(in a slide tray, in my Zio Tek Media Carousels),
and
they might have subparts(track name). The basic difference is the
headings
that would appear on queries and reports.

My initial thought is to create a "base" table that contains the
intrinsic
name of the item (primary key), basic common information columns, and
column(s) containing indicators (you can tell I was a programmer)
describing
the basic phyical characteristic(s) if the item, such as if it is a CD or
power tool, where it is physically located, etc. This table would then be
linked to a table (and possibly subtables) which would contain enough
columns to describe the "details" of the item. For some items, some
columns
or subtables wouldn't be used.

This way I could create more generic queries and reports by using VBA and
the values of the indicator columns to dynamically change the headings,
etc.
to match the appropriate item. Some storage might be wasted for columns
that
are not used by some items, but I would rather have a unified system than
one
that uses the least disk storage.

Whenever you have 1...n things that you want to show dynamically on a form
or a report, you are talking rows, not columns. So even though the 1-n
things are bits of data about various things in your database, you need to
find a way to make those things rows.

Probably the easiest would be something like this:

ThingTypes (Defines types of things that could be described in your db)
ThingTypeId
ThingTypeDesc

ThingTypeProperties (Lists properties a given thing type could have)
PropertyID
ThingTypeId
PropertyDesc
PropertyDatatype
PropertyFieldlength

Things (things that are actually being described)
ThingID
ThingDesc
ThingTypeID

ThingProperties (actual properties of a given thing)
ThingID
PropertyID
PropertyValue

The issue with this is that Access forms don't lend themselves to this type
of structure, so you'll need to use a Left join of ThingType with
ThingtypeProperties to allow you to "pull" a list of properties to fill out
about a particular thing of that type.

HTH;

Amy