I'm hoping to get some advise regarding the design of an inventory database for large home appliances.
I need to track model, serial, date arrived, arrival number, date delivered, and delivery number.
As you can imagine we often will have one model number with multiple serials. It seems obvious to me that I need a one to many relationship here between the models and serials which I have set up. I have one table that lists the model number and a sub table with the serial, arrival number, and delivery number fields.
When units arrive in our warehouse they are assigned an arrival number based on the date of arrival. There is one arrival number per date and each arrival number can have multiple units. I would like to relate arrival numbers to each serial. Seems like another one to many relationship.
When units are delivered to the customer they are assigned a delivery number. There will be several serials relating to any delivery number.
Our current database simply lists all of this information on one table. This works but is just about as easy to use as a giant spreadsheet. Entering new units is a pain because you have to enter lots of repeated data.
I would like to set up relationships so that I can enter an arrival number and then input the units that have arrived under that number.
Once the units have been entered into the database they will be removed by delivery number (this is essentially an order number). I would like to be able to set up an order and assign multiple units to that order.
My end goal is to make the database easy to use as several people with varying levels of computer skill need to access.
Before I get to far into the design I would like some suggestions or confirmation that I am on the right track!
Does anyone have suggestions or ideas for how best to layout my relationships?