Any Improvement on these tables please?

  • Thread starter Thread starter Bob H
  • Start date Start date
B

Bob H

I blundered into building a tool inventory database a few months ago,
and now I would like to see if it can be improved upon.

Some basic information:
It is not purposly relational, although there some relationships between
certain fields, and all the tables do not contain all the same fields.

Some tables have 14 fields depending on the type of information required
for the type of tool, but other tables have 12 fields or less.
The tables have only 6 common fields, all other fields are either unique
or specific for that type of tool.

I seem to have gotten myself bogged down with how it is, and I am
looking at how I can improve it if possible.

Thanks
 
I blundered into building a tool inventory database a few months ago,
and now I would like to see if it can be improved upon.

Some basic information:
It is not purposly relational, although there some relationships between
certain fields, and all the tables do not contain all the same fields.

Some tables have 14 fields depending on the type of information required
for the type of tool, but other tables have 12 fields or less.
The tables have only 6 common fields, all other fields are either unique
or specific for that type of tool.
Click to expand...

The technical term for this is "subclassing", but that's not so
important. What is important is that your tables are usable and
easily-queried. Right now they have a few issues, not the least of
which is that you have redundant fields (the shared 6) in multiple
tables.

In most cases like this it's simplest to have all your tools in one
table, with a ToolType field (best as a lookup to another table) that
identifies each type. Then you can put all the fields in that table,
the 6 shared ones and the unique ones too.

It's perfectly okay for fields to be empty (Null) if they don't apply
to that kind of tool. Empty fields use up hardly any space. You can
even disable those fields on a data-entry form when the record is for
a tool that doesn't use them.

Having them all in one table allows you to query them all together a
lot easier, for example to create a report or form listing all tools
regardless of type.

If you want to dig into this a bit more, I recommend Database Design
for Mere Mortals by Michael Hernandez.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Armen said:
The technical term for this is "subclassing", but that's not so
important. What is important is that your tables are usable and
easily-queried. Right now they have a few issues, not the least of
which is that you have redundant fields (the shared 6) in multiple
tables.

In most cases like this it's simplest to have all your tools in one
table, with a ToolType field (best as a lookup to another table) that
identifies each type. Then you can put all the fields in that table,
the 6 shared ones and the unique ones too.

It's perfectly okay for fields to be empty (Null) if they don't apply
to that kind of tool. Empty fields use up hardly any space. You can
even disable those fields on a data-entry form when the record is for
a tool that doesn't use them.

Having them all in one table allows you to query them all together a
lot easier, for example to create a report or form listing all tools
regardless of type.

If you want to dig into this a bit more, I recommend Database Design
for Mere Mortals by Michael Hernandez.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Click to expand...

Thanks for the reply and help, but I have a question or two here.

Regarding putting all the tools in one table, as mentioned I have 8
tables with a total of just under 3000 records, not many I grant you,
but it felt like ten times that when I was inputting the information!
So do you mean to take all the records and input them into just one
table or just the types of tools?

Thanks again
 
Regarding putting all the tools in one table, as mentioned I have 8
tables with a total of just under 3000 records, not many I grant you,
but it felt like ten times that when I was inputting the information!
So do you mean to take all the records and input them into just one
table or just the types of tools?
Click to expand...

Hi Bob,

Yes, I suggest taking the 3000 tools and appending them into one Tool
table. That's a very small record count for an Access table.

You'll have one field in table Tool called ToolTypeKey, which will
have a relationship to the primary key field in the ToolType table.
That's where your 8 different ToolTypes will be stored. You can use a
combobox on a form to select the correct one for each Tool.

Then you can have reports and forms that show your entire tool list
with the common fields. You can enter values into the
tooltype-specific fields only when they are applicable, otherwise
leave them empty.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back
Top