Database design issue regarding serialization

[vba317]

I am designing a database but I have a design issue I was hoping to get some input from this great community. I am designing a database that is going to track products by serial number for a service department. The table is pretty basic so far so good. I am going to set up the column where the serial number is stored as no duplicates. So far easy. The problem I am trying to overcome is the units come back. So when the operator goes to enter the unit from the form connected to the table, the operator would get an error because the serial number is already in the database. Do I add a table for units that have been returned twice another table for three times and so on. If I use this approach what kind of code would I use to make sure the serial number went into the right table. Any thoughts?

 

[dhookom]

Sounds like you should have a table of products and a table of product movements. One would have the serial number as a unique value and the other would not.

Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

Usual practice is to have an Item Master File, that is where all the things that describe your item. Your SN would be the key field to that table. You then need a table that would usually be called an Inventory Transaction file. This file would have a one to many relationship to your IMF. The Transaction file would in turn be supported by a Transaction Type table, which would list every possible event that would apply when an inventory item changes status. The key to this table is your SN, and the transaction type and perhaps other things that make it unique, like cust id, date and time of transaction, or you could simpy use the record number using an Access autonumber field. The Transaction Type table would appear as a pulldown somewhere on your data entry . Everytime something is sold, returned or scraped, it is recorded as a transaction in the in the I-Trans table. Combining all these tables will get you the reports you need from data that has relational integrity.

 

[vbajock]

Usual practice is to have an Item Master File, that is where all the things that describe your item. Your SN would be the key field to that table. You then need a table that would usually be called an Inventory Transaction file. This file would have a one to many relationship to your IMF. The Transaction file would in turn be supported by a Transaction Type table, which would list every possible event that would apply when an inventory item changes status. The key to this table is your SN, and the transaction type and perhaps other things that make it unique, like cust id, date and time of transaction, or you could simpy use the record number using an Access autonumber field. The Transaction Type table would appear as a pulldown somewhere on your data entry . Everytime something is sold, returned or scraped, it is recorded as a transaction in the in the I-Trans table. Combining all these tables will get you the reports you need from data that has relational integrity.