Stock control/movment db - design confusion

[bakerajatha]

Having problems with a Stock control and movment database. Im gaving trouble with the relationships between defining stock and their locations. I have at the moment

Table 1
PartID Autonumber, primary key)
ModelPartID (type of equipment, defined in further table)
Badge
Serial
Status

Table2
stockID Autonumber, primary key)
StockLocation
partID
Quantity

My problem is that a part will either have a Badge/serial number in which case it must be only ever one part in one location
( ie IBM Monitor badge a12345 is in location 'repair shelf')

However a non serialed part may have the same part may have multiple lications and multiple quantity
(i.e. i have 2 mice in 'stock' and 3 mice in 'repair shelf')

As a further complication the badges items must always have a location, ( in-accept, stock, customer site x etc), howeever the non-badged parts does not need a customer location ( nobody cares) , only the various stock/repair locations


Now have I got my tables/relationship ok and just have to keep plugging away at forms and their VB, or has someone done this before and can suggest a better(easier) way to go?

 

[hneal98]

It sounds like you need more than two tables. Putting the Badge and the Serial in the same table would be a mistake becuase of the relationship. I would split the two. That way you can have the relation with the badge and the relation witht he Serial at the same time. The same would hold true for your badge vs non badge.

I would create a Badge table and a Serial table or you might have stock vs repair shelf.

That is my opinion. Someone else might have different thoughts.

 

[bakerajatha]

Thanks hneal98

Can I dissagree with you on one point
badge is a company issued number which put on any peice of equipment having a serial number, which simplifies identification of that particular item( think barcode ). A particular item has only one badge number and one serial number and therefore I contend should be part of the same table.

As to the second part thats what I was wondering if the tables should be split (enen if not normalised) so as to make my life a bit easier

 

[hneal98]

Point taken. However, it is important to remember that you don't want duplication in any of your tables if you can help it, so if you have a situation where an item can show up in one case only once and in another case multiple times, it might be better to seperate them. However it is your call since I haven't seen the whole picture. Also, I don't know the relationship between these Serial/badge numbers and the repair vs stock shelves either.

The rule of thumb is usually to create each table with it's own theme and to try to avoid duplication.

 

[johnherman]

I think you need to cover the situation where parts can have multiple locations and pretty much ignore the badge/serial needing only one location - although you could enforce that with a trigger or programmatically at the VB level.



-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[bakerajatha]

Thanks jonherman,
while I havnt seen the light its possable you've given me a glimmer, mabye a form showing equipment and a subform showing locations and quantities,

so that in a form
- part a ( no badge) will show subform which displays location x and location y and location z
- however part b (badge and serial) will still show a subform but only ever have one record to display, say of location w

As you say, could be not so much fun controling this in VB but its at least somthing to be working on tomorrow

Meanwhile if there is anyone else whose played with asset control/movment DB's , let me know if im headed in the right direction

 

[hneal98]

Well, looks like you have it figured out. Have fun.