Delima on structure: Order details

[RickDB]

My database uses a common structure (from what I understand):

tblCustomers
tblOrders
tblOrderDetails

so, when an item is entered in, the following is recorded (the field names may not be right, but you 'll get the effect):

tbOrders:
TaxRate
ShippingCost

tblOrderDetails:
Quantity
ModelName
Price

Calculated totals on the form/reports:

Extension (line price for each order detail)
SubTotal (Sum of Extensions)
Taxes
Total

So I have struggled and finally made this work, but here is the problem:

Say I want to record the serial number for each product sold. What is a good method to do this? Obviously I cannot store it in the OrderDetails table because each line/entry could account for any quantity of that model.

I know a one to many is required, has anyone done this before and can you spare some advice? I want to make sure I get the structure right because I will have to alter a significant amount of my database to implement this feature.

Thank you!!!!

 

[RuralGuy]

I would say that any serialized item will limit the quantity to 1 per line item.

 

[RickDB]

Here's what I've come up with so far:

tblOrders
- OrderID
- ShippingCost
- TaxRate
- REST OF FIELDS (irrelevant)

tblOrderDetails
- OrderDetailsID
- OrderID
- Quantity
- ProductModel
- SerialNumberID
- Price
- Extension

NEW TABLE:
(Maybe I should name this tblProductSN to make sure it is more clear what it is?)
tblSerialNumber
- SerialNumberID
- OrderID OR(?? ??) OrderDetailsID
- SerialNumber
- SoldDate
- ProductModel
- ContainerID

Should I tie the new serial number table directly to the tblOrders or tblOrderDetails?

Could someone look this over and see if any problems jump out at them, I always appreciate any help / advice!

Thank you!

 

[RickDB]

Thanks RG, are you saying that it would be unwise to use the Quantity / Model design here and I should just go with individual items?

That would work fine for typical orders, but if someone ordered a container full of 30 - 50 products, that might become a pain....

Thanks

 

[RickDB]

If I did go with the Many to One relationship to handle this, I was thinking it may be more simple to just use OrderID as my FK instead of OrderDetailsID to make queries easier... that was I can avoid having to include that extra table in my queries when I pull this all back together...

Hell I don't know, help a brotha out!!!

 

[gemma-the-husky]

Tricky one this Rick.

Serial Numbers are a real headache. I am thinking about these for another project. The trouble is, is that if you have a stock of say 20 items, then you have to ignore the Serial No, otherwise you would have to treat every serial no independently. The best you can do I think, is treat the serial number as a note/memo field, and store the info as text in the tblOrderDetails. If you sell several then store all the numbers.

Thinking about this in general, why is the serial important at all (rhetorical question). If its for traceability or maintenance issues then in general its probably not a problem, as proof of owership can be identified in other ways.

I suppose it depends on exactly what the product is. How would a car manufacturer deal with Car VIN nos, or a HiFi man'f deal with HiFi - I think they do it by a after-sale warranty registration system, rather than in their production stock system.

 

[RickDB]

Whaddup once again Dave, (I'm proving to be quite the problem poster huh)

I will give you an outline (you asked for it!):

Our database has two functions: Sales and Service

CustomerID is the easy way to tie those together, but then you get into one other thing: Containers. We import product from overseas & manufacture domestically, all of these come to us in containers on semis. I want to be able to tie all of this together to make our system much more powerful than it currently is.

A serial number makes the following things possible:
Warranty Claims
RGA (Returned Goods Authorization)
Tracking Product Versions (By Container Load)
Parts Lists
Product Transfer ("I bought one of your lifts off of my buddy Jim Bob")

That's a generic overview, but if I can nail that down, it makes many things possible.

For instance, we order by container, and there may be modifications to a product, so it would be great to know what container a customer's product came from, make notes about that shipment and have it all readily available for service calls, etc...

I could get really long winded here, but here is a generic overview:

 

[The_Doc_Man]

There is no way to handle this problem that doesn't involve either "doing it right" or violating normalization rules.

If you want to track multiple serial numbers in a single transaction, you will need a table that lists each serial number in a separate record. The record will probably be the many-side of a one-to-many relationship off of the PO number by which those items were sold.

The PO number would of course ALSO be the many-side in a many-to-one relation to the customer ID, assuming you have repeat business with your customers. So your chain of ownership is a two-layer JOIN of individual items to PO and PO to user.

Any other thing you might try to do to avoid the work will violate all rules of normalization. Down the road, usually about the time someone comes up with a new report you had never considered before, you will come to hate any decision that does not excruciatingly preserve normalization.

I can understand why one would wish to preserve S/N as a method that can be used to validate warranty calls or licensing issues. Or many other reasons. My belief here is that, tedious or not, ugly or not, it is a cost of doing business in a particular way. If that is what it takes, so be it.

 

[gemma-the-husky]

In the above situation, when you sell something you would presumably have to know which SNo product you had sold to pick it off the serial no. That's why i think (say Currys) don't do it that way. I think they must have a record of serial no's purchased, but then just sell products and only tie them up with SNo's when warranty registrations are received. eg Can the SNo be idenitifed without opening the box anyway?

I may be wrong, because i'm just thinking of the concept. I haven't worked in any environment dealing in SNo's previously.

 

[RickDB]

Thank you for the insight Doc.

What is your evaluation of the super generic outline I provided in the graphic above? Do the relationships appear to be workable?

I am starting to realize this may prove to be a nightmare months from now, but if I can make something that will work, it would be just a huge help to us.

 

[RickDB]

In the above situation, when you sell something you would presumably have to know which SNo product you had sold to pick it off the serial no. That's why i think (say Currys) don't do it that way. I think they must have a record of serial no's purchased, but then just sell products and only tie them up with SNo's when warranty registrations are received. eg Can the SNo be idenitifed without opening the box anyway?

I may be wrong, because i'm just thinking of the concept. I haven't worked in any environment dealing in SNo's previously.

What happens is there is a front office employee who processes orders, and assigns serial numbers with the following method (roughly):

- Container's order date
- Load number (2 letters (manufacturer) & 4 #'s (order # from that manufacturer)
- model number (3 letter combo)
- product number (4 didigt number)

Serial number generation will always be done manually, never by computer... So there would have to be a form where the order processor enters the information in.

I hope this makes sense, because it confuses the hell out of me!

 

[gemma-the-husky]

I don't know what your product is - can you say, irts easier to think about it?- but when a customer buys one, do you therefore book one out of stock, and then search for the one with the correct serial no to give him, or do you make a note of the SNo on the product for later processing, or ndo you not ot even record the SNo anyway.

And when your order processor notes the serial numbers, how do these physically get put on particular products?

 

[RickDB]

We sell heavy duty equipment, tools, automotive repair machines, things of that nature... One sample product would be an engine crane. Garage stuff like that... too many to list.

We are a paper organization now, with spreadsheets all over the frikin' place!

We will continue to do hand counts of inventory for the time being, we make notes of backordered items (is it common pratice to log backorders in a db? [I'm sure it is] Do people store that data in a seperate table then?).

I could have the front office person log serial numbers into Access (currently done in Excel - I plan to change that, hence these posts) as containers come in, then have the person in charge of shipping verify and assign products by serial number to a certain order.

SN's get onto products by metallic labels we tag. I am looking at moving to a heavy-duty clear vinyl pocket, which we would be able to slide a scannable tag into with a bar code, SN and a few other choice details - hopefully to simplify inventory counts (we are getting help with this part).

How convoluted is this starting to seem!?


I've taught myself to build pretty decent websites (our current company site is about 200 pages deep, all built from scratch, pictures, everything) in the past year, now I plan on focusing on DB's and also tying that back into websites with a dynamic page programming language like ASP .Net (2.0), PHP, Rails, whatever I can get into and make work for me - I realize that last step is a doozie!

I am a Marketing Director from a print background btw, all this web / database / vb stuff is still very new to me, but I fully intend to become mildly proficient in the next year or so - sorry if I come across as daft!

 

[RickDB]

There is no way to handle this problem that doesn't involve either "doing it right" or violating normalization rules.

If you want to track multiple serial numbers in a single transaction, you will need a table that lists each serial number in a separate record. The record will probably be the many-side of a one-to-many relationship off of the PO number by which those items were sold.

The PO number would of course ALSO be the many-side in a many-to-one relation to the customer ID, assuming you have repeat business with your customers. So your chain of ownership is a two-layer JOIN of individual items to PO and PO to user.

Any other thing you might try to do to avoid the work will violate all rules of normalization. Down the road, usually about the time someone comes up with a new report you had never considered before, you will come to hate any decision that does not excruciatingly preserve normalization.

I can understand why one would wish to preserve S/N as a method that can be used to validate warranty calls or licensing issues. Or many other reasons. My belief here is that, tedious or not, ugly or not, it is a cost of doing business in a particular way. If that is what it takes, so be it.

Doc, I have read this again and would like your thoughts on this, tracking PO's doesn't seem to necessitate cycle redundancy:

tblCustomers > tblOrders > tblOrderDetails > tblSerialNumbers

I was asking before if it would be more wise to tag the tblSerialNumbers directly to tblOrders and bypass tblOrderDetails to simplify the relationship, but with this, it seem slike I should be able to get the desired effect right?

Thanks Doc!

 

[gemma-the-husky]

Order systems can get very complex. Managing sales orders/purchase orders/ stock leads into backorders, and therefore presumably delivering part orders, fulfilling order remainders, managing stock order levels, etc and a host of similar problems.

The problem with any system is that easy things are easy to do, but hard things are offten hard to do, and the books stop just where it gets interesting!

Managing traceability must come into the hard part I think!

 

[RickDB]

Agreed!

We are looking at a MAS 90 / Sales Logix implementation as soon as budget will allow, but for now it is imperitive to get rid of all of our different spreadsheets for each step of the sales / service process and eliminate redundant data entry so we don't have to hire more and more people to do inefficient work!

I know whatever system I string together will be nowhere near as effective as a professional solution, but it beats the hell out of paper and entering in one customer into 15 different tracking systems!

I will avoid many of the more complex ERP type functions that are way out of my league, but this serial number thing tying Service to Sales seems very possible.