Early stages of shop-stock database design

[Guest]

I work for a drum store and every year I watch the staff go through a
laborious method of collecting an inventory of stock onto separate Excel
spreadsheets. The obvious solution would be one database. However, I need to
walk before I can run so I decided to se if I could start out with a small
database design that would deliver information about all the drum heads we
had in stock, including the different makes, models, quantities and cost
prices. My theory is, if I can get this database model right, then it should
follow through for other products we sell.

My early thoughts are that I will need at least 3 tables:

Brand name
Model name
Size

The Brand name table would have at least 2 fields:

ManufacturerName & ManufacturerID

The Primary Key would be the ManufacturerID field

The Model name table would have at least 2 fields:

ModelName & ModelID

The Primary Key would be the ModelID field.

The Size table would have at least 2 fields:

Size & SizeID

The Primary Key would be the SizeID field.

Relationships would be as follows:

There would be one Brand name to many Model Names

There would be one Model Name to many Sizes

However, because I need to somewhere include quantities and prices I am now
wondering whether my initial database design is flawed. I'm trying to think
out the most efficient use of tables without resorting to having to resort to
a single table along the lines of:

ManufacturerName, ModelName, Size, Quantity, Cost

This is because I want to be able to create a data-input front-end with drop
down lists of the manufacturer name, model name and size. I can see this
being possible by using separate tables but I can't see how I can do this by
using just one massive table full of mixed brand names and model names.

Any thoughts from an experienced Access user would be gratefully received!!!

 

[Guest]

I think that you are putting the cart before the horse. You are talking
about the nature of information being recorded and desired FE features and
skipped past the question of, fundamentally, what constitutes a record in
your table or tables. Most likely the answer is each of the products that
you stock or offer. If so, that's one answer = one table. Then you can add
all of those other features later, including dropdown lists. But your "drop
down list" as you describe it sounds more like the main database (table) than
a dropdown list.

Hope this helps a little.

 

[Guest]

Thanks for the thoughts FredFred. It helps to bounce these ideas out. Yes, I
am probably trying to run before i can walk here, having had more experience
in reading about database design than actually doing it.

So, I guess I could have one table with all drum skins on it, with each
record having information about brand, model, size, quantity and cost.
However, is there a danger of putting too much overhead on a table by firing
multiple queries at it? That's why I thought about splitting the brands,
models and sizes into different tables so the stress from queries would be
minimized.

 

[Guest]

You're making a very simple database. Nothing you're going to do will even
cause Access to blink. So not only could you put all your drums into a
single table.. you can put every single product in the entire shop into that
table. Access can handle tables with MILLIONS of records each containing
hundreds of fields.

I think the first thing you need to do, before you set up tables.. before
you worry about drop down lists and combo boxes.. is to clearly define what
the end result of your project is going to be. What need is it filling.
What information do users need to meet this need. When you've got that
figured out.. then go back and figure out your table design.