Edit: Ok, just read a bunch of supertype/subtype articles and I think they are suggesting Idea 2.5 (below). So please don't bother with that disgusting wall of text I put together.



Hi all,

Having read a lot of Microsoft Access 2013 Bible: The Comprehensive Tutorial Resource, I have a decent understanding of normalizing tables, queries, and forms (have not started the reports or VBA chapter yet). I made some good progress in the design and a couple data-entry-type forms. But ran into a problem that I tried to Google extensively to no avail. So I hope someone can help me.


We are a construction company with a field crew that we supply equipment and consumables to for their work. I'm trying to set up a database that will catalog all our assets (equipment), quantify all our consumables, track their history, and hopefully provide some statistics.

Question: How should I setup my Object tables if I have objects with different field requirements? And how should I set up Transactions tables with some same field requirements and some different if I want consolidated transaction queries/reports?

For assets, we have several types of transactions, i.e. delivery, return, maintenance, repair, and disposal. Each have both overlapping and unique data types. For example, transaction dates are common in all transaction, but only delivery has location. For consumables, we only have delivery and return transactions. Consumables also have different information from asset, e.g. quantity on hand.

Now I am going to need to show reports and export data were all transactions are consolidated, e.g. an assets full transaction history where date and transaction type would be columns.


In trying to figure this out, I've come up with two and a half ideas.

Idea 1:
Consolidate my objects (assets and consumables) into one table with fields for both, see below:
tblAllObjects
ObjectID Object AssetModel SerialNumber ConsumablesOnHand
1 Nailgun ModelX 123456789
2 Nail 20
(I reduced the number of fields, to simplify things.)

And I could do the same for transactions:
tblAllTransactions
TransID Type Date ObjectID Quantity Location Repair
1 Delivery 12/1 1 (Nailgun) Job 001
2 Delivery 12/1 2 (Nail) 1 Job 001
3 Repair 12/2 1 (Nailgun) New Trigger

I'm pretty sure I will be able to make all the reports and forms I need if the tables are set up as such. But in my reading, this causes bloat because of the empty fields and is a violation of normalization.


Idea 2:
Individualize the objects and transactions, and link them with join tables.
tblAssets
ObjectID Object AssetModel SerialNumber
1 Nailgun ModelX 123456789

tblConsumables


ObjectID Object ConsumablesOnHand
2 Nail 20

tblJoinObjects
ObjectID
1
2

tblJoinTransactions
TransID
1
2
3

tblAssetDelivery
TransID Date ObjectID Location
1 12/1 1 (Nailgun) Job 001

tblConsumableDelivery
TransID Date ObjectID Quantity Location
2 12/1 1 (Nail) 1 Job 001

tblAssetRepair
TransID Date ObjectID Repair
3 12/1 1 (Nailgun) New Trigger

I feel like this is more normalized. But I don't know if I can consolidate the information into a query (or some other way) when I need.


Idea 2.5:
Somewhere in between idea 1 and idea 2. So something like this
tblAssets
ObjectID AssetModel SerialNumber
1 ModelX 123456789

tblConsumables
ObjectID ConsumablesOnHand
2 20

tblJoinObjects
ObjectID Object
1 Nailgun
2 Nail

Is this the perfect hybrid that combines the best of both worlds? Or is this some horrendous creation that you are ashamed to have even heard?


I understand that everyone's time is important, so even general instructions on the table and a direction for research into the query setup would be phenomenal. Thank you ahead of time!