Solved

Need advice on the best way to get my data "Normalized" to display on ASPX page

Posted on 2013-06-19
3
610 Views
Last Modified: 2013-06-26
First off I know this topic has been discussed by me a couple times on this site. I am here now to explain in detail what I have and what I need. Any advice is greatly appreciated.

A little background on my company
My company sells new aftermarket parts for tractors, combines and other types of farm equipment. You can check our website out below.
www.abilenemachine.com

We have 3 stores in Kansas, South Carolina, and California.

Our parts that we sell have a unique part number attached to them. We call it our AMI part number. (AMI = Abilene Machine Inc). We have over 50,000 numbers that we catalog and stock in our inventory.
In most cases we try and take the OEM part number and add a "AM" to the beginning of it to make our AMI part number. In some cases this is not possible though.

The process now
Many times we get orders from different people/companies that give us a list of part numbers and they want to know if we have them available. As it is right now each part number has to be individually researched to see if it matches a part in our catalog. The reason for the research is because of OEM sub numbers. OEM is the original equipment manufacturer, ie John Deere, Case IH, etc. Here is a little info on sub numbers

OEM Sub Numbers Explained
When the OEM develops parts they assign an OEM part number to it. Down the road, could be a month/year/etc, the part design might change just a bit. The OEM then issues a new part design and give it a "new" OEM part number which is actually a sub number.
BUT, that does not mean the original part number will not work anymore. Both original and sub numbers will both work for the application they are used on. If something changes that makes the part not work the number won't sub OR it will require other parts to make it work. But I don't want to get focused on those details because for this question i am only wanting you to know that OEM part numbers and OEM sub numbers are the same part.
Sometimes an OEM part number can have multiple sub numbers. I have seen some have up to 8.

The process now (continued....)
So you see since those sub numbers are present it takes research to see if any of the OEM part/sub numbers matches our numbers. The person doing the research has to go into the OEM catalog searching the part number provided and see if that number subs (or is a sub) then check all those part/sub numbers against our AMI part numbers.

This would also be beneficial for other tasks not mentioned here.

What I am needing done
I figured the solution to this problem was to create a web page that users could go on and easily find if our AMI part number matches any of the OEMpart/sub numbers.
This is what i came up with
http://bakerabilene.com/interchange.aspx

That offers the users 2 ways to do their research. They can input one OEMpart/sub number at a time OR they can upload a list of OEM part/sub numbers via a csv file.

Problem is that when i was doing research on getting everything setup the way it is now I was being told that my data was not normalized and I needed to do that before i could have everything function properly. So I stopped with development and looked at my data and that is where I am stuck.

The steps I have done so far
These tables were pulled from an OEM catalog and was the only way we could get the data out. Below is an example of my JD table (all other OEM tables are setup this way)

JD Table
---------------------------
OEMPartNumer | OEMDescription | OEMSubNumber
AR57112   -   USE PN AR82761   -   AR82761
AR82761   -   USE PN AR88536   -   AR88536
AR88536   -   Lever

Open in new window


And here is my AMI part number table that has a matching OEM part number

AMI table
---------------------------
Item            | OEMItem   | Description
AMAR65123       | AR65123   | Axle
AMAR88536       | AR88536   | Lever

Open in new window


So the problem is that all the OEM part/sub numbers have to be matched against the [OEMItem] from the [AMI table]
Example - If i was to search OEM number AR57112 it would not find it. Because AR57112 doesn't associate with AR88536.
If I was to search AR82761 OEM number that would work because it associates with AR88536 which it matched on OEM Item.

So that is when i figured out that having table in that format would not work. So i tired to do a 9 step query in access to match all the oem part/sub numbers and put them on one line. It kind of worked but left me with many duplicates and I was still being told the data was not normalized and need to be.
I was able to actually get the table working that i did the 9 step query on by using a cross apply sql statment. That was only one table. below you see how much data im working with.

How much data i'm working with
Right now i have the following 3 OEM tables. Agco, JD, Case New Holland. Each have around 1 million records in them. I also have my AMI table that has 50,000 records.

What to do?
I got the ASPX page built and functions fine. I have my SQL server working. All i'm lacking is my data.

So how can i get my data normalized? I assume there has to be a faster way than going down each record one by one.

I got some advice along the way about normalization and it stated i should have 1 unique field and everything else tie to it. So would the unique field be OEM Part Number? It would contain all unique part number whether they are original or sub? then in another column I would have [Original number]. That would specify the original number for the sub number?

Im just not familiar enough with data to make an expert decision. That is why im reaching out to experts here to know how to proceed.

If the tables do have to have extensive work done is this something i should contract out to an online contractor?

Maybe I have went about this all wrong? That is why im providing what i need done and the data i have been given.

Conclusion
Any advice is greatly appreciated. If I can just get my data right this would work great for my company. I hope that i have provided enough information to give experts a good understanding of my situation. Thank you for any replies. I will award points to any good advice.

The ultimate goal is to have a user input a single OEM part/sub number OR a list of OEM part/sub numbers and have it match to the OEM number(whether is the original or sub number) on my AMI table and show the AMI part number.
0
Comment
Question by:bignadad
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39260055
While it's not technically normalized, I would add a column to the JD / OEM parts table to indicate the current (final) part number for all parts, like so:


OEMPartNumber | OEMDescription | OEMSubNumber | OEMCurrentPartNumber
AR57112       - USE PN AR82761 - AR82761      - AR88536
AR82761       - USE PN AR88536 - AR88536      - AR88536
AR88536       - Lever                         - AR88536


When users enter a PartNumber, you lookup the OEMPartNumber column, but you return the OEMCurrentPartNumber.

When a new part is added, causing AR88536 to become a sub-part, do an UPDATE on the JD table to replace OEMCurrentPartNumber = AR88536 with the new number.

The initial, one-time load of the new column will take some code and processing.  But then triggers should be able to maintain the data with no additional coding/effort.
0
 
LVL 2

Author Comment

by:bignadad
ID: 39271124
I was really hoping to get more feedback on a solution but maybe there isn't anything else I can do here?

I do appreciate your feedback thought Scott.

I do have one question. Doing the method you are suggesting would require me to also make sure that the OEM.Item column on my AMI table match the OEMCurrentPartnumner, right?

So a user searches for any OEM Sub number it makes its match in the JD OEM table and compares the OEMCurrenPartNumber from the matched line to the OEM Part Number on the AMI table.

Is that right?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39271873
Yes.  The current part must be in the AMI table -- but the older, subnumbers would not have to be in that table, only current data.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now