Adding a Subset table (1 Viewer)

[SeanDelere]

I have been writing a stock control database for my small Jewellery Shop and thanks to all the help and encouragement I have received here can see the light at the end of the tunnel - until I reaised a major design error!

My system is based around every product having a unique Product Code in the form ABC1234

AB is a 2 letter code for the Supplier
C is the category (Ring, Bracelet, Pendant etc)
1234 starts at 1 and gets incremented by 1 for each different product

The problem is that I have no current way of recording different sizes for rings with the same Product Code. So when I run a stock level check I may have 3 rings with the code JWR0021 and if my reorder level is set to 2 I would not reorder any of them. The ring may be a top seller in larger sizes but nobody buys them in the smaller sizes, I am sure you can see my problem.

What I want to do is to add another table for ring sizes but still keep the same Product Code. That way I can keep the code as ABC1234 but have A,B,C etc as subset of data for sizes.

What would be my best way of doing this without having to re write everything?

As usual any suggestions will be most welcome.

 

[KenHigg]

Would it be pos to add another char at the end of the Product Code that would signify the size?

kh

 

[SeanDelere]

I think that maybe the easiest way to go although I was hoping to come with an alternative to save an awful lot of rewiting everything.

 

[KenHigg]

Hum...

What about the 1234 part. Would it be pos to add logic to here? Say the first two chars equal product type and the second two would be the size?

kh

 

[SeanDelere]

Thought about that one too. The problem is that I would then only be able to have 99 of each Category from each Supplier and I have alredy gone over the 100 mark with a couple.

 

[KenHigg]

I hate to bring this up at such a late point in your development, but these are the kinds of issues I have heard made against trying to build 'smart' id numbers.

In the future maybe it would be better to place each of these components in a separate fld and concat' them on the fly when needed...

kh

 

[SeanDelere]

No Problem Ken. As anyone who has been following my progress over the past few months will know this is not the first mistake I have made and I very much doubt it will be my last.

I have decided to bight the bullet and add an optional extended part to the Product Code. So now the Product Code can be either
ABC1234
or
ABC1234/A+

The /A+ part is the ring size with a + if it is half ring size.

I have updated the relevant tables with this new extended code and am in the progress of seeing what needs rewriting.

Sean

 

[KenHigg]

Just one last thought - Would it be prudent to add some additional non relative char places to the code field just in case you made them for some other anomaly you may encounter...



???
kh

 

[SeanDelere]

Good idea Ken.

I will ask my partner if there are any other things she can think of as I can't at the moment.

There is no point in having more than 4 digits as jewellery designs come and go very quickly and I already have a system to archeive old Product Codes releasing them for use again.