Inventory Database questions

[JD2775]

Hi all,

I have a question on coming up with proper tables and relationships for an inventory database. This will not be a typical inventory database but more of an ordering alert database. Let me explain..

All inventoried items at work have a laminated 3x5 card that is placed next to it. On the card is a barcoded label, with an unique ID number. When something is running low (a re-order point) the card will be taken to the computer and the ID number scanned into a FM field in a layout. When this happens I would like other fields on the layout to populate (ID number, Name, Vendor, Vendor Phone Number, Re-Order Level, Order amount and Last Re-Order date) Once all inventory is in, an email script will be triggered and a pdf (or Excel, havent decided) will be sent to the ordering supervisor that the order needs to be placed.

My question is, how would you set up the tables. The fields I have mentioned are the following

ID Number (unique for each product)

Name

Vendor

Vendor Phone Number

Re-Order Level (pre-set value associated with ID number)

Order Amount (pre-set default value that can be changed, only field that will be manually filled at point of scanning)

Last Re-Order Date

How would you split these up into related tables I guess is my question? What primary keys would u assign? Any input or even advice on how to make this more efficient would be appreciated!

Thanks

[Drew Sanderson]

There are still a lot of details missing in your description and my file but this sample file may give you a start.

Ask any questions you like.

Hope this helps.

inventory.fp7.zip

[JD2775]

Thank you Drew for taking the time to come up with that. Ok, I guess I am more confused now The Z-create and Z-modify fields in each of the tables, what exactly do those do? Are they just functions or calculations behind the scenes?

Also, what are the 4 non-"related" tables up top for? Do they serve a purpose beyond the 4 tables underneath that are linked?

Also, please tell me what details I am missing. Any additional info I can provide that would make this easier, I would be happy to

Thanks again Drew!

[Drew Sanderson]

I was afraid it might make you more confused. Figuring it out will actually push you to the next level.

the z_create and z_modify fields are just good practice to keep track of when a record is created/modified and by whom.

The "non related tables" are really table occurrences (TOs) and act as a legend to the tables below. All "tables" on the relationship graph are really TOs. You may want to do some reading on Anchor-Buoy to understand what I have done in the sample.

Some questions include:

Do the product identifiers already exist?

Do products have more than one vendor or could the vendor change with time?

[JD2775]

Thank you Drew, and for your patience!

I will read up more on TO's, I think it makes sense but I will delve into it more.

Also, knowing who added or modified a record is also a good thing to have, thank you for thinking of it

To answer your questions

Some questions include:

Do the product identifiers already exist?

Yes they will exist for each product, and as a new product comes along it will be assigned a new number that will be stored in the database, along with related contact and other info

Do products have more than one vendor or could the vendor change with time?

Good question, yes some products could have more than one vendor, and the vendor could certainly change over time

Thanks again Drew

[Drew Sanderson]

Since you already have product numbers, you will have to add a field for that data. You will probably want to maintain the key fields I set up separate from the product identifier you will be using to be sure your primary keys are in fact unique.

You probably have enough to chew on for a while.

Write again when you are ready for more. :)