CODEWITHSUNDEEP
excelms-accessdatabase-normalization
MoralesJosue
MoralesJosue

Reputation: 199

How to normalize this table inventory

I have this excel sheet and I want to migrate it to Access (in the near future some other DB manager) And I don't know how to normalize it exactly, I know this might be very opinion base. Currently they use this table for inventory

This is the original Table (sheet)

"TableName: Parts", Fields:"Id_Part", "No_Part", "No_Mold", "No_Lot", "Rev", "Description", "Area", "No_Job", No_Batch,"OrderDate","RecivedDate"

Explanation of problem:

ok the idea is to create a DB that stores all the part numbers the "x" company has, these part numbers have the corresponding field:

1.- Id_Part : is the unique number for each part.

2.- No_Part: Number part of each part that the company uses for there products.

3.- No_Mold: Each Part Number uses a Molding Item, some part numbers use the same Molding Item.

4.- No_Lot: The Lot Number is to keep track of the part numbers in case the client has some issues with the final product. (Its like a tracking number).

5.- Rev: is for Revision control example: A, B or C.

6.- Description: Describes the part number.

7.- Area: name of the department in with the part number is used ( like a type of Part Number).

8.-No_Batch: Its similar to the Lot number, but its an internal number for the company.

9.- Order Date: Date in witch we ordered a part number form a provider.

10.- Received Date: Date when we get that part number from the provider.

This is how I tried to Normalize it

Table1 Name: Parts

Fields: Id_Part, No_Part, Id_Mold, Id_Lot, Id_Rev, Id_Description, Id_Area, Id_job, Id_Batch, Date_Order, Date_Recived.

Table2 Name: Areas

Fields: Id_Area, Name

Table3: Molds

Fields: Id_Mold, No_Mold, Id_Part

Table4:Jobs

Fields: Id_Job, No_Job

Tablr5:Batchs

Fields: Id_Batch, No_Batch

Table6 Name: descriptions

Fields:Id_Description,Description,Id_Part

Table7 Name:Rev

Fields: Id_Rev,Rev,Id_Part

Any help is useful.

Upvotes: 0

Views: 520

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

It seems like the PartRevision is the main table here rather than the part. You don't order a Honda Accord, you order a 2013 Honda Accord.

You purchase a PartRevision and it goes into a batch and a lot. You sell a part revision and it pulls from a batch and a lot. Here's how I'd set it up.

enter image description here

Upvotes: 1

Related Questions