Database Normalization - Question

[blyxx86]

I am working on a shipping/receiving module for a service database. Well, in a sense, it is a movement module. Moved out of warehouse, moved into warehouse.

I'm wondering if these two tables are different, or if they are the same and just need a column added called "direction" (ie, in or out).

They would be recording the same exact fields, but just opposite directions. Which leads me to believe that they are not related to each other in that sense.

I have not been working with databases for too long (less than 1 year), no formal schooling on them, so this is all new to me, but I have had great luck with a test database that was running them as two separate tables.

Another issue would be inventory would become more complicated if I use one table. It would be two SELECT [QTY] FROM tblMovement WHERE (([Direction])=0/1). Although, that might be more efficient as it is the same table. I'm not sure... This is why I need your help.

I'm open to your opinions on this and would appreciate your input.

[Atli]

Hey.

Does your database store information about the item beeing moved in a separate table?

It would seem logical to me to store a list of items, including the ammount of said item that is in stock, in a separate table, and the movement log in another table.

That way, if you "moved" a quantity of an item out of the warehosue, you would decrease that number, if you moved it in, you would increase it.
This increase/decrease would then be logged. The log table would contain the amount moved. The amount would either be positive or negative, which would indicate the direction in which it moved.

[r035198x]

..
This increase/decrease would then be logged. The log table would contain the amount moved. The amount would either be positive or negative, which would indicate the direction in which it moved.

Yep, just one Material_Transa ction table should be sufficient.

The columns for that would be
id, movementdate, qty,warehouse_I D,movementtype, movementtype_ID

They qty would be + if the quantities are added into the warehouse and negative otherwise. The movement type and movement typeID are references to the actual movement transaction/document.

[coolsti]

One of the nice things about databases is this: A good database design models real life entities. Tables are nouns, columns are adjectives. So if you find yourself in a situation like you were in with this post, where the proper table structure seems questionable, then you could maybe stop yourself and consider whether you have the proper tables in the first place.

The two previous replies gave you a better table structure: a "noun" in real life is your item, and so you need a table for that. Another "noun" in real life is a an item transaction (movement in or movement out) and so that would also make a good table.

[blyxx86]

Thank you everyone for the help with this.

I was thinking about it a little bit this morning and decided it would be MUCH simpler to have one movement table for numerous reasons. I realized there are certain instances in the receiving where a 'shipping>shipp ing_details>shi pping_details_d etails' and a receiving counterpart would have become extremely redundant (which is exactly what normalization is supposed to cure).

I think I was having a problem with shipped quantities and received quantities both be positive numbers in the same table. However, the "MovementTy pe" would be a modifier to their integer values and of course a "Shipping" view and "Receiving" view would also fix that problem.

I find it amazing that a little over a year ago I knew nothing about databases and nearly nothing about writing scripts or any type of code that wasn't HTML. I feel like I've gone very far in terms of where I'm at and where I was. This community has helped me greatly!

[blyxx86]

Hey.

Does your database store information about the item beeing moved in a separate table?

It would seem logical to me to store a list of items, including the ammount of said item that is in stock, in a separate table, and the movement log in another table.

That way, if you "moved" a quantity of an item out of the warehosue, you would decrease that number, if you moved it in, you would increase it.
This increase/decrease would then be logged. The log table would contain the amount moved. The amount would either be positive or negative, which would indicate the direction in which it moved.

It would be stored in a related table, a 'details' table, if you will. Model information would also be a FK to a model table.

I always used the structure found here (http://www.allenbrowne .com/AppInventory.ht ml) as a guide for keeping track of inventory. However, now that I am normalizing data I began to raise questions. I'm glad I did.

[blyxx86]

Yep, just one Material_Transa ction table should be sufficient.

The columns for that would be
id, movementdate, qty,warehouse_I D,movementtype, movementtype_ID

They qty would be + if the quantities are added into the warehouse and negative otherwise. The movement type and movement typeID are references to the actual movement transaction/document.

I understand the movementtype_ID being related to the table 'movementtype' (ie shipped/received/destroyed/etc...) but I'm a little lost with the 'movementtype' column itself. Could you divulge a bit more information why you would have both there?


Also, do you think it's better to have the application control the +/- aspects of receiving/shipping or just do some type of trigger to make them negative, or just store the two queries (shipped/received) as separate views and compile onhand inventories through that?

[r035198x]

I understand the movementtype_ID being related to the table 'movementtype' (ie shipped/received/destroyed/etc...) but I'm a little lost with the 'movementtype' column itself. Could you divulge a bit more information why you would have both there ...

Actually movementType_ID would store the ID of the actual shipment document. i.e You would probably have a Shipment table containing other shipment info like customer, shipmentAddress , e.t.c. The movement type would then differentiate between say shipped and destroyed. Without it one cannot tell what the source of the movement was because movementtypeID would just contain the ids. One way of dealing with this (without using movement type) would be to have ids for each movement type in the table. e.g shipmentID, receiptID, e.t.c.

Also, do you think it's better to have the application control the +/- aspects of receiving/shipping or just do some type of trigger to make them negative, or just store the two queries (shipped/received) as separate views and compile onhand inventories through that?

There's no need for any triggers here. A material document (shipment, receipt, e.t.c) when created/captured should be responsible for updating the transaction table with the appropriate sign.

[blyxx86]

Actually movementType_ID would store the ID of the actual shipment document. i.e You would probably have a Shipment table containing other shipment info like customer, shipmentAddress , e.t.c. The movement type would then differentiate between say shipped and destroyed. Without it one cannot tell what the source of the movement was because movementtypeID would just contain the ids. One way of dealing with this (without using movement type) would be to have ids for each movement type in the table. e.g shipmentID, receiptID, e.t.c.


There's no need for any triggers here. A material document (shipment, receipt, e.t.c) when created/captured should be responsible for updating the transaction table with the appropriate sign.

So, what you're telling me is that I might have multiple tables for things like (Shipment/Receipt) but have only one "Movement" table? Which, in a sense, would be the 'details' section of the "Shipment" or "Receipt" ? Am I following you correctly?

I will be playing with all of this stuff soon in the database so I will find out if it works or not.