Bad Design Choice need to Redesign

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Bad Design Choice need to Redesign

Author

Topic

finito
Starting Member

18 Posts

Posted - 2010-03-01 : 11:03:40

I like to think my Sql knowledge is moderate. I have made a bad design choice.

I am not sure what you need to help me solve my problem.

My problem is complex and I will try my best to explain. Please ask if you don't understand. At least I think it is.

Here is My Table structure looks like:

ProductID (Primary Key), Location (Primary Key), SerialNo, Cost, Stock, Brand etc.

Example Entries:


ProdID | SerialNo          | Brand |Cost  | Stock | Location |
 5213  | 1234774           |  XY   |12.00 |   5   |   SH1    |
 5213  | 1477512           |  XY   | 4.00 |   1   |   SH2    |
 3814  | 1234774 (778122)  |  XY   |12.00 |   3   |   SH2    |
 7844  | 778122 (1234774)  |  ON   | 9.00 |   1   |   SH1    |

The program is supposed to query and Return "WHERE SerialNo Like '%query%'" so if query = '1234774'

It would return All except the second entry.

The problem is The Frontend shows location Based entries so Frontend would do this "WHERE SerialNo Like '%query%' AND Location = 'Loc'" Where Loc is SH1 ~ SH15 and WH1 ~ WH5.

I implemented Replacement Numbers using () in SerialNo field using string functions. Now I have SerialNos' some with Replacment Numbers and others without.

What I want to do is Somehow link all the Replacement numbers for all locations.

I don't have duplicate entries for The Same SerialNo and Brand for one location.

FrontEnd sees the following as the same but in different locations:


ProdID | SerialNo          | Brand |Cost  | Stock | Location |
 5213  | 1234774           |  XY   |12.00 |   5   |   SH1    |
 3814  | 1234774 (778122)  |  XY   |12.00 |   3   |   SH2    |

This is a replacement Number and will also show up:


 7844  | 778122 (1234774)  |  ON   | 9.00 |   1   |   SH1    |

What I want to do is


 5213  | 1234774 (778122)  |  XY   |12.00 |   5   |   SH1    |
 3814  | 1234774 (778122)  |  XY   |12.00 |   3   |   SH2    |


ProdID | SerialNo          | Brand |Cost  | SH1 | SH2 | ~ | WH1 | ~
 5213  | 1234774 (778122)  |  XY   |12.00 |  5  |  3  | ~ | 205 | ~

I think it is worth mentioning Not all SerialNos' exist on all Locations.

I forgot to mention that some SerialNo have more than 1 replacement numbers. i.e.


 5213  | 1234774                    |  XY   |12.00 |   5   |   SH1  |
 3814  | 1234774 (778122)           |  XY   |12.00 |   3   |   SH2  |
 54711 | 1234774 (778122 / 122234)  |  XY   |12.00 |   2   |   WH1  |

Thanks in Advance.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2010-03-01 : 21:02:56

I'm pretty sure I don't understand quite what you're up to but your data model needs improving before you can address this.
Are you trying to say there is a relationship between serial numbers?
If so, what is it? I can't see from your data how you related things, nor how you decide when to show a replacement. I started to think any serial number with a "replacement" would show all cases every time it was selected but that's not the case according to some other examples.

Either way, you need to model that properly reflects your data, using a table not strings. Maybe a serial# to serial# relationship of some kind.
Maybe if you can elaborate on my questions I can help more specifically.

finito
Starting Member

18 Posts

Posted - 2010-03-02 : 04:43:25

quote:
Originally posted by LoztInSpace

I started to think any serial number with a "replacement" would show all cases every time it was selected

This is the case, I am sorry I confused you. After re-reading I see how that happened. I should have re-read what I wrote.

"WHERE SerialNo Like '%query%'" so if query = '1234774'"
will return


ProdID | SerialNo          | Brand |Cost  | Stock | Location |
 5213  | 1234774           |  XY   |12.00 |   5   |   SH1    |
 3814  | 1234774 (778122)  |  XY   |12.00 |   3   |   SH2    |
 7844  | 778122 (1234774)  |  ON   | 9.00 |   1   |   SH1    |

Lets assume I am running the Frontend from SH1 in this case the code would be:

"WHERE SerialNo Like '%1234774%' AND Location = 'SH1'"

This will Return


ProdID | SerialNo          | Brand |Cost  | Stock | Location |
 5213  | 1234774           |  XY   |12.00 |   5   |   SH1    |
 7844  | 778122 (1234774)  |  ON   | 9.00 |   1   |   SH1    |

Adding Replacements is done in the Frontend so what happens is some Serial# are replaced in some locations and not others beacuse I left the adding Replacement up to the user.

quote:
Originally posted by LoztInSpace

Are you trying to say there is a relationship between serial numbers?

I am not sure what you want here.

Serial#'s in each location with the same Number without the () and the same brand are the same items in different locations.

I would like to re-iterate ProductID and Location are Primary Keys.


ProdID | SerialNo          | Brand |Cost  | Stock | Location |
 5213  | 1234774           |  XY   |12.00 |   5   |   SH1    |
 3814  | 1234774 (778122)  |  XY   |12.00 |   3   |   SH2    |

Maybe this will help.

serialNo.substring( 0 , serialno.indexof( '(' ) - 1 ) AND Brand AND Location Could alternatively be set as Primary Keys.

If you are asking is there any SQL relationship (one to one, one to many, many to many). I thought this is only possible from table to table, but I am talking about one table.

If I completely missed the question please tell me so.

Thanks in advance

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2010-03-02 : 08:05:07

So are you saying you want to
a) get all rows for a location/serial number
b) get all rows from that set with a related serial number irrespective of location?

Becuase that's easy (literally do the steps as described using a CTE).
Otherwise I still don't get what you are trying to do I am afraid.

And yes you can have relationships within another table. You can have a serial number table that has a replacement linked back to itself.

Something like

create table sn
( sn int,
replacement int references sn.sn not null)

if that helps.

You can then work out all related replacements from a known starting point.
I'm a bit tired so I think I am missing your point still.

finito
Starting Member

18 Posts

Posted - 2010-03-03 : 05:37:28

quote:
Originally posted by LoztInSpace

So are you saying you want to
a) get all rows for a location/serial number
b) get all rows from that set with a related serial number irrespective of location?

That is a good part of the solution.

Hmm This is the easiest way I can think of trying to explain this:

Problem:


111 012 12 14 (MRT1005)
1233557 (1447X744 MRT1005)
1447X744 ( / 1233557)                
1234471 (1233557 / )
11 21 1 322 547 (QTT147 / MRT1005) ()

Solution: ???

Result:


111 012 12 14 (MRT1005 / 11 21 1 322 547 / 1447X744 / QTT147 / 1233557 / 1234471)
1233557 (1447X744 / MRT1005 / 11 21 1 322 547 / 111 012 12 14 / QTT147 / 1234471)
1447X744 (11 21 1 322 547 / 1233557 / 1234471 / 111 012 12 14 / QTT147 / MRT1005)
1234471 (1233557 / 1447X744 / 111 012 12 14 / QTT147 / MRT1005 / 11 21 1 322 547)
11 21 1 322 547 (QTT147 / MRT1005 / 1447X744 / 111 012 12 14 / 1234471  /1233557)

David Singleton
Starting Member

35 Posts

Posted - 2010-03-03 : 08:25:24

I am not a SQL expert, but I do know about database design. Many coding issues I see are because the original design was wrong, and instead of throwing everything out and starting again, developers work with the design and it just eventually turns to spaghetti.

LoztInSpace said right at the begining that you need to sort out the data model, but that comment seems to have slipped though the cracks, even though its the most important thing.

At a first guess, I think you need a model more like:


ProdID | SerialNo  | Original SerialNo | Brand |Cost  | Stock | Location |
 5213  | 1234774   |                   |  XY   |12.00 |   5   |   SH1    |
 5213  | 1477512   |                   |  XY   | 4.00 |   1   |   SH2    |
 3814  | 1234774   |                   |  XY   |12.00 |   0   |   SH2    |
 3814  | 778122    | 1234774           |  XY   |12.00 |   3   |   SH2    |
 7844  | 778122    |                   |  ON   | 9.00 |   0   |   SH1    |
 7844  | 1234774   | 778122            |  ON   | 9.00 |   1   |   SH1    |
 54711 | 1234774   |                   |  XY   |12.00 |   0   |   WH1    |
 54711 | 778122    | 1234774           |  XY   |12.00 |   0   |   WH1    |
 54711 | 122234    | 778122            |  XY   |12.00 |   2   |   WH1    |

if you dont fix up the model now the situation will just get worse.

Also your sample data looks strange. I see a lot of developers that type random numbers for test data, and I always recommend to use realistic data, other wise things don't make sense. For example hao can two different parts have the same serial number?

Stop worrying about the programming for a bit. Sit down and fix up the model. Get out Excel and type in some more realistic data and then look at what you need to achieve. THEN create a design and THEN start programming.

David Singleton

Microsoft MVP Dynamics NAV

finito
Starting Member

18 Posts

Posted - 2010-03-03 : 09:30:18

quote:
Originally posted by David Singleton

Stop worrying about the programming for a bit. Sit down and fix up the model. Get out Excel and type in some more realistic data and then look at what you need to achieve. THEN create a design and THEN start programming.

That is the reason I am here. I need to fix the design but I can't think of a more efficient way.

The following will not work:


ProdID | SerialNo  | Original SerialNo | Brand |Cost  | Stock | Location |
 5213  | 1234774   |                   |  XY   |12.00 |   5   |   SH1    |
 5213  | 1477512   |                   |  XY   | 4.00 |   1   |   SH2    |
 3814  | 1234774   |                   |  XY   |12.00 |   0   |   SH2    |
 3814  | 778122    | 1234774           |  XY   |12.00 |   3   |   SH2    |
 7844  | 778122    |                   |  ON   | 9.00 |   0   |   SH1    |
 7844  | 1234774   | 778122            |  ON   | 9.00 |   1   |   SH1    |
 54711 | 1234774   |                   |  XY   |12.00 |   0   |   WH1    |
 54711 | 778122    | 1234774           |  XY   |12.00 |   0   |   WH1    |
 54711 | 122234    | 778122            |  XY   |12.00 |   2   |   WH1    |

Replace Numbers are unset a SerialNo may have infinite replacement numbers.

What you did is break the primary key ProdID and Location. (maybe intentionally as part of the solution)

This how the serialNo column really looks like. There is no Typo.


111 012 12 14 (MRT1005)
1233557 (1447X744 MRT1005)
1447X744 ( / 1233557)                
1234471 (1233557 / )
11 21 1 322 547 (QTT147 / MRT1005) ()

If I can somehow fix this to make it look like what the result looks like.
I can make the frontend fix the rest so this problem doesn't arise again.

David Singleton
Starting Member

35 Posts

Posted - 2010-03-03 : 09:47:24

Can you explain then what you mean by replacement serial numbers, give an example, go through it step by step.

David Singleton

Microsoft MVP Dynamics NAV

finito
Starting Member

18 Posts

Posted - 2010-03-03 : 11:37:00

quote:
Originally posted by David Singleton

Can you explain then what you mean by replacement serial numbers, give an example, go through it step by step.

Ok Lets Say we have:

Product A of Brand OY
Product A of Brand DE
Product A of Brand SR
Product B of Brand OY

Now Lets Say:
Product A of Brand OY has a SerialNo 1234578
Product A of Brand DE has a SerialNo 17D844
Product A of Brand SR has a SerialNo BP741
Product B of Brand OY has a SerialNo 7844258

Now
Product A of Brand OY should be either 1234578 (17D844 / BP741) or 1234578 (BP741 / 17D844) in all existing locations
Product A of Brand DE should be either 17D844 (1234578 / BP741) or 17D844 (BP741 / 1234578) in all existing locations
Product A of Brand SR should be either BP741 (17D844 / 1234578) or BP741 (1234578 / 17D844) in all existing locations
Product B of Brand OY should be 7844258 // assuming it has no replacements

Right now it looks like:


BP741 (1234578 / 17D844)   | WH1
1234578                    | SH1
1234578 (17D844)           | SH5
17D844 (BP741)             | SH12
BP741 (1234578 / 17D844)   | SH1
7844258                    | SH2
7844258                    | SH3
BP741 (17D844)             | SH6

etc. You get the picture right?

David Singleton
Starting Member

35 Posts

Posted - 2010-03-03 : 12:26:08

I am lost. You seem to be using "SerialNo" to define something different to what I would think of as a serial number. I am sorry, but I really can not understand what you are trying to do.

What are the numbers in parenthesis supposed to be? Are they a string of serial numbers? Or is somehow the serial number built up like this?

David Singleton

Microsoft MVP Dynamics NAV

David Singleton
Starting Member

35 Posts

Posted - 2010-03-03 : 12:28:26

quote:

Originally posted by finito


ProdID | SerialNo          | Brand |Cost  | Stock | Location |
 5213  | 1234774           |  XY   |12.00 |   5   |   SH1    |
 3814  | 1234774 (778122)  |  XY   |12.00 |   3   |   SH2    |

Also how can you have multiple parts with the same serial number. A serial number has to be unique, so you can't have a quantity of 5 or 3 etc.

David Singleton

Microsoft MVP Dynamics NAV

finito
Starting Member

18 Posts

Posted - 2010-03-04 : 03:16:30

quote:
Originally posted by David Singleton

I am lost. You seem to be using "SerialNo" to define something different to what I would think of as a serial number. I am sorry, but I really can not understand what you are trying to do.

I think you are confusing ProductID with SerialNo.

Let me define SerialNo for you:
SerialNo is the Product AlphaNumeric Code on the Packaging to identify the product.

Manufacturer of Brand OY will have a different SerialNo from that of Brand ON for the same product hence using replacement Numbers.

quote:
Originally posted by David Singleton

Also how can you have multiple parts with the same serial number. A serial number has to be unique, so you can't have a quantity of 5 or 3 etc.

I have already mentioned a SerialNo and Brand is unique to it's location, however I assumed it was understood that each location may have a different quantity.

I have told you I made a bad Design choice.

I have offered a possible solution, I need help in implementation.

David Singleton
Starting Member

35 Posts

Posted - 2010-03-04 : 04:07:13

quote:
Originally posted by finito

I think you are confusing ProductID with SerialNo.

No! I am quite certain I know what Serial Number and Product Number are.

A product ID will distinguish completely different items from one another. So a company might sell a Hub a Router a Switch and a USB WiFi adapter. All three generically do similar things, but they are clearly different and for different purposes so each has its own Unique Product ID (or SKU or Item No.). Now say I pick a specific Switch, maybe I purchase three of them, each of them will have an identical Product ID, but each will have a different serial number. If a Switch breaks and needs to go back for repair, it's important to know the serial number, since that identifies WHICH of the three was faulty, and when I purchased it and if it is under warranty. It also lets the manufacturer track back the manufacturing batch to see if they need to recall other similar switches.

So every serial number must be unique to be able to identify that Particular product, thus it's impossible to have 5 Products with the same serial number.

If you can work this out and understand what it is that you are trying to resolve, then you can move forward in designing a better data model. Once you have a good data model, writing code will be easy.

PS the parenthesis are still confusing the issue.

David Singleton

Microsoft MVP Dynamics NAV

finito
Starting Member

18 Posts

Posted - 2010-03-04 : 06:54:31

quote:
Originally posted by David Singleton

No! I am quite certain I know what Serial Number and Product Number are.

A product ID will distinguish completely different items from one another. So a company might sell a Hub a Router a Switch and a USB WiFi adapter. All three generically do similar things, but they are clearly different and for different purposes so each has its own Unique Product ID (or SKU or Item No.). Now say I pick a specific Switch, maybe I purchase three of them, each of them will have an identical Product ID, but each will have a different serial number. If a Switch breaks and needs to go back for repair, it's important to know the serial number, since that identifies WHICH of the three was faulty, and when I purchased it and if it is under warranty. It also lets the manufacturer track back the manufacturing batch to see if they need to recall other similar switches.

So every serial number must be unique to be able to identify that Particular product, thus it's impossible to have 5 Products with the same serial number.

If you can work this out and understand what it is that you are trying to resolve, then you can move forward in designing a better data model. Once you have a good data model, writing code will be easy.

PS the parenthesis are still confusing the issue.

OK I see where the confusion is.

ProdID is used in the Frontend and holds no value related to the actual product itself.
The user in Frontend will never see ProdID mentioned anywhere.

ProdID and SerialNo are not what you think it is for my design.

ProductID is used to make the entries Unique

Hope this helps

David Singleton
Starting Member

35 Posts

Posted - 2010-03-04 : 07:22:44

Well I must give in then. Sorry, but I really don't understand what you are trying to do. Maybe you can explain it step by step, or maybe someone else can help, but I can't help if I don't understand what you are trying to do. Good luck.

PS often (well actually ALWAYS

) it is better to explain WHAT you are trying to achieve, rather than HOW you want to do it. It leaves more room to give good answers.

David Singleton

Microsoft MVP Dynamics NAV

finito
Starting Member

18 Posts

Posted - 2010-03-04 : 09:12:55

quote:
Originally posted by David Singleton

Well I must give in then. Sorry, but I really don't understand what you are trying to do. Maybe you can explain it step by step, or maybe someone else can help, but I can't help if I don't understand what you are trying to do. Good luck.

PS often (well actually ALWAYS ) it is better to explain WHAT you are trying to achieve, rather than HOW you want to do it. It leaves more room to give good answers.

David, I seriously don't know what you don't understand. But then again my English teachers always told me my communication skill are next to nothing.

I will try again:

I have a table called Products

Products table looks like:


ProdID   | SerialNo                                  |  Brand  | Stock |  Location |
 51234   | 111 012 12 14 (MRT1005)                   |   OT    |   1   |     SH1   |
 57711   | 1233557 (1447X744 MRT1005)                |   XO    |   3   |     SH1   |
 87114   | 1447X744 ( / 1233557)                     |   RO    |   1   |     SH1   |
 77412   | 1234471 (1233557 / )                      |   QH    |   5   |     SH1   |
 788412  | 11 21 1 322 547 (QTT147 / MRT1005) ()     |   BE    |   4   |     SH1   |
 78841   | 111 012 12 14 (MRT1005 / 1447X744)        |   OT    |   1   |     SH2   |
 98789   | 1233557 (1447X744 MRT1005 / 1233557)      |   XO    |   3   |     SH2   |
 967871  | 1447X744 ( / 1233557)                     |   RO    |   1   |     SH2   |
 411     | 1234471 (1233557 / )                      |   QH    |   5   |     SH2   |
 77511   | 11 21 1 322 547 (QTT147 / MRT1005)        |   BE    |   4   |     SH2   |

Now the SerialNo Column is in a mess.

I wish to clean it up.

The Frontend is a POS. When the user enters a serialNo. The frontend needs to display all matches and replace numbers as well.

Since I didn't know the replace numbers and they were numerous, I left the adding replace upto the user.

The easiest way I could think of doing so was like how i did it.

The problem now is that I wish to fix the entries by either combining same serialNo and Brand Combo's so that when someone adds a replace number it is added universally

something like this:


ProdID   | SerialNo                                  |  Brand  | Stock |  Location | 
 51234   | 111 012 12 14 (MRT1005)                   |   OT    |   1   |     SH1   |
 78841   | 111 012 12 14 (MRT1005 / 1447X744)        |   OT    |   1   |     SH2   |

would become

 ProdID | SerialNo                                                                                   | Brand | SH1 | SH2 | 
 52134  | 111 012 12 14 (MRT1005 / 1233557 / 1447X744 / 1234471 / 1233557 / 11 21 1 322 547 / QTT147)|  OT   |  1  |  1  |

all other entries that start with "111 012 12 14 (" would essentially be deleted and moved into another table like the this:

or something like this:


 ProdID | SerialNo                                                                                   | Brand | Stock | Location | 
 52134  | 111 012 12 14 (MRT1005 / 1233557 / 1447X744 / 1234471 / 1233557 / 11 21 1 322 547 / QTT147)|  OT   |   1   |    SH1   |
 78841  | 111 012 12 14 (MRT1005 / 1233557 / 1447X744 / 1234471 / 1233557 / 11 21 1 322 547 / QTT147)|  OT   |   1   |    SH2   |

Just update all entries like this.

Does this help?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-04 : 12:30:55

Hmm.I'm no sql expert too but i would never do what you are doing.On the first example you create something that will be very hard to get the data after, on the second example you are creating multiple columns of the same data.
I would just created a new table with id and serial no(the MRT10... etc) and would reference it here.
So p.e. ProdID 52134 or SerialNo 111 012 12 14 will have 7 rows to reference in the new table (MRT1005),(1233557),(144X774) etc.
Then create a sp that will look at the numbers entered and if match then put the number in the new table, reference the column and delete the entry of the first table so you can have the unique product id you want.
If you products add directly to the first table you can create a trigger to do the removal job for you.

finito
Starting Member

18 Posts

Posted - 2010-03-04 : 16:09:28

quote:
Originally posted by sapator

Hmm.I'm no sql expert too but i would never do what you are doing.On the first example you create something that will be very hard to get the data after, on the second example you are creating multiple columns of the same data.
I would just created a new table with id and serial no(the MRT10... etc) and would reference it here.
So p.e. ProdID 52134 or SerialNo 111 012 12 14 will have 7 rows to reference in the new table (MRT1005),(1233557),(144X774) etc.
Then create a sp that will look at the numbers entered and if match then put the number in the new table, reference the column and delete the entry of the first table so you can have the unique product id you want.
If you products add directly to the first table you can create a trigger to do the removal job for you.

This I thought of but I have no clue how to do in SQL. can you do an if in SQL?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-04 : 19:48:30

Of course you can do an if in SQL.
But i would suggest first to create your new table and then if you don't know how to do the sp, there are a lot of guys(and ladies) here that have help me much too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources