Table(s) with 100s of Serial Numbers?

[sp00k]

Hey, I am creating a inventory database and I want to keep in normalized, but in this situation I'm not sure if I can.

Basically a shipment comes in and from that shipment all the serial numbers, part numbers, part description, and Manufacturer number has to be recorded. The problem is most shipments are a bit different then the last one.

My table right now has over 100 table fields for each item (we have 9 different items that have to be recorded. for example, Box1, Box2, Box3, Box1a, Box2a, Box3a, Box1z, Box2z, Box2z.

All these items are slightly different, and each Box contains a multitude of smaller items. So I would have a table for Box1 which contains about 25 items each having serial numbers, part numbers, part description, and Manufacture number, though, some items don't have all these.

Right now I have 9 tables, each having about 100 slots for number input.

SerialNumA1 PartNumA1 PartDescA1
SerialNumA2 PartNumA2 PartDescA2
ect...

I could separate Serial Number into its own table, but then I run into problems with the relationship, as it would get messed up I believe because not all parts have serial numbers.

Any ideas? sorry for the length, its just a weird project.

 

[The_Doc_Man]

Don't EVER do that. You have a repeating group.

This might be complex to do, and might involve multiple queries to build what you need, but this is NEVER acceptable in a single record of a permanent table.

Box1, Box2, Box3, Box1a, Box2a, Box3a, Box1z, Box2z, Box2z.

You should have a few main tables. If the part numbers are uniform across the whole set, then do something like this:

tblShipment
ShipID, Prime Key, whatever you are using; autonumber is as good as anything else
ShipDate, RcvdDate, Carrier, etc. - whatever you are tracking that is unique to a shipment.

tblVendor
VendID, Prime Key, whatever you are using; autonumber is OK
other vendor information.

tblBoxes
BoxID, a slot for the box number.
ShipID, foreign key >> many::1 >> tblShipments
Anything else you track about boxes.

It is not clear from your description how many boxes you can get and whether the number actually means anything. If the box it came in has no meaning to the business, this is a red herring that will complicate your life. But... if there is a chance that you need to know which box it came in, perhaps for shrinkage or damage control, keep that in the box table. EITHER: BoxID is the prime key, unique across all shipments, OR the combination of BoxID and ShipID is the compound prime key, the BoxID recycling per shipment.

tblPartsList
PartNumb, Prime Key, whatever your part numbering scheme happens to be
PartDescr, text
PartName, text
Anything else that is unique to part numbers.

tblSerials
PartID, FK many::1 >> tblPartsList
ShipID, FK many::1 >> tblShipments
BoxID, FK many::1 >> tblBoxes
Serial number, possibly with an index that allows duplicate S/N
Anything else you needed to keep about individual parts.

Not clear from your description: Are parts uniquely obtained from a single vendor? If so, the vendor ID is in the partslist table. If not, it might go either in the Shipment or Box table if one of those is always single-vendor. If and only if one box can come with a mixed bag of vendors, the vendor ID will go in tblSerial.

How you get it this way is either a data entry issue with forms and a lot of drop-downs.

Note that the serial number table doesn't use the serial number as a prime key. Therefore, if you happen to have parts with no serial number, you can handle it.

This only addresses the incoming side of the business. If you need to track the same type of information in the outgoing side of the business, this adds fields to the individual parts table for whatever you use for outgoing shipments.

 

[sp00k]

thanks so much, its much clearer now. We don't actually get a physical box, its basically a shrink wrap of small boxes(no id). Some of the items have vendor id on them. The amount of shrink wrap containers we get really depends, anywhere from 18 or so a week. The small items in them range from 25-50 per shrink wrap container.
I'll get start on this database this weekend, if I have more question I'll ask you, thanks!

EDIT: On my form I need a section were the box type can remain the same, but still have multiple serial numbers. is that possible still?

 

[gemma-the-husky]

serial numbers is the tricky one

normally in stock systems you have x items of something, and they are all homogeneous. having serial numbers cuts completely across this, as it means you dont have homogeneous items - so at what point do you actually deal with or use serial numbers - because is germane to the problem i think

 

[sp00k]

Thanks for the reply gemma(though I had to use wiki on two of your words, heh)
Serial Numbers seems very tricky indeed. We are only using the serial numbers to keep track of our items(lets say radio transceiver). Sometimes other company's use our items and if they have their own broken radio transceiver they try to switch it with our good one. We need a way to keep track what is ours and what is theirs.

The database would be easier, if in every shipment we get a certain amount of x items. But in reality every shipment can be totally different with brand new parts, and I can't really create tables on the fly for each new part.

hopes this sheds some light on the subject

Still going to try the doc_man idea today/tomorrow, so hopefully he's ready for more question hehhttp://www.access-programmers.co.uk/forums/member.php?u=3758

 

[gemma-the-husky]

the problem is that you cant just use a relational dbs as you would normally if you need to log serial numbers against sales purchases.

in this case arent just selling a widget - you are selling a widget with a particular S No.

i dont think this is easy at all

 

[sp00k]

Yeah, it seems a bit tricky. I re-made it, at least this time its a bit more noramlized

 

[The_Doc_Man]

Here's the question I have to ask. Please don't take it as trying to insult your problem analysis ability. Rather, it is more along the lines of verifying a requirement.

If I read this right, you send - and get back - items. Perhaps on a service basis, perhaps as a rental thing - doesn't matter for this question. Are you treating these devices as being sold or being rented. (And if both, we need to know that, too).

My goal is so that we can figure out how to treat this DB you describe. Categorizing it as a sales environment is one issue, as a rental is another, and as a sales+service is a third. (Mixed bag would be the fourth.)

See, that requirement of tracking S/N is killer because ordinary inventory controls don't work quite the same way when the set of items remains individually identifiable as when they lose their identity once in inventory. And that is where we need to have a good mental image of product flow to give best advice.

It is entirely possible that best advice might be to split this into two parts, tracked S/N and non-tracked S/N inventory, as long as the parts don't overlap between the two. Normally, you wouldn't do that because of normalization, but in this case normalization actually suggests that you might be FORCED to split if it is the mixed-bag case. Before I drag this out, I need to see your answer.

 

[sp00k]

We only receive the parts(we don't sell them) we actually use them at work sites. But sometimes a 3rd party installs our parts, and we don't want them switching their parts with ours. so I suppose it would be a rental(kind of). Right now I have it

a table for Serial Number/part number/Manufacturer Number/Part Description Called PartInformation.

I then have a table for radio, a table for antenna,and a table for Material. Each table, has site location, and site Number(should normalize this even more) So I know where each shipment is going to end up. Each shipment is in either one of these categories. I have a one to many relationship. So any radio, antenna or material can have many parts(part number, serial etc.) and I have the form displaying as such.

Seems to be working out, but I'm sure I can make it more efficient or even more normalized (or probably re-done completely)

Any advice would be excellent

 

[gemma-the-husky]

i spent a lot of timer thinking about S. Nos (eg, in a white goods scenario, for warranty purposes) i came to the conclusion that possibly the supplier doesnt know what S No he has sold, until the customer contacts to register the product for warranty

i just cant see how it could work otherwise

your situation is a bit different to this, and as the whole thread demonstrates, the best solution really isnt obvious