How should I structure my table need help I'm building an auto recycling database 1

[toyden]

Hi all I need help with a table structure. I'm building a database for a friend who has a auto recycling shop now I'm trying to put together a table for a stock list.

So far it is called tbl_stocklist
It has p/key:stock_list_id
Car_number
Vin_number
Car_colour
Body_type
Etc
what I need to know is how do I add the parts that the car has that can be re-used will I have to add each part individually or just a parts field.? As a car has many parts.
this will need to be search able later.

Thanks in advance for your help
regards,
Jesse

 

[MajP]

You need to read up on relational database design. You can google and find lots of links. You will see the term "normalized" or "normal" design which means proper database design. You will need to learn how to then join your different tables. Definately spend some time learning the basics, because it will save in the long run.

You will have to have at least the following tables (but likely many more as it grows)

tblVehicles
Vin_number 'if all your cars have a unique vin then this is a good natural primary key
Car_number
Car_colour
Body_type
'other fields that uniquely identify a vehicle

tblParts
partName
partType
partDescription
partSerial
'other fields that uniquely describe that part
vin_number_FK 'you would add a foriegn key that relates the part to the parent vehicle

Now you have a one to many relationship. Multiple "child" parts would relate to a single parent car. You can then use queries to join the data to enter or display it. You would want to learn how to build a main form with a subform to enter and show a car and all of its parts.

 

[johnherman]

An additional consideration, from someone who shops at junkyards quite often. Sometimes parts from different years are interchangeable. For instance, I wanted a black hood for my 2004 Civic and the 2005 model uses the exact same hood. You might want to check out car-part.com

If your friend wants to cross-list at car-part.com, they probably require a particular database structure.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[toyden]

Thanks guy's for your guidance should I post what I have so far, so you have a better idea of what is going on so far? I understand the basic structure and a one to many relationship well at least I think I do its just that I'm not sure how best to store the information. I'll put up pics soon of what I have.

 

[toyden]

here are the screen caps they show what tables i have and what each table contains, i hope this helps you guys to help me, i can be a bit thick sometimes.

thanks in advance
Regards
Jesse

 

[toyden]

well this didn't work ill try again hope this works.

 

[MajP]

I see some repetition of data.
For example if Vin Number is your PK that is the only field needed to join your tables. no need to repeat car number in the tables. In the sales tables you should only have the part ID, and no keys to the car table. Because A sale relates to a part and the part automatically relates to the car table. So I think you can remove car number from every table (except the car table). In the sales table remove the vin and car number.

 

[toyden]

so my sales table now has sale_id, Part_id do i need to keep Name_of_part, Position_on_the_car and colour or will that data be obtained from somewhere else.

ill apologize now i have not worked in access for a good 8 years so i will be looking for lots of help to get this job done.


thanks
Jesse

 

[MajP]

That is information that is unique about a part, therefore that information would be stored in the parts table.

 

[dhookom]

I suggest you find and use a naming convention that doesn't allow spaces. You seem to use underscores in place of spaces in some field names but not others.

I prefer EngineType to either [Engine Type] or Engine_Type. It's up to you but consistency is a nice touch.

You might also want to review spelling. No one should ever see actual field names other than you as the developer but correcting field and table names after the fact is not fun.

I would probably move to a more normalized table structure. For instance tbl_Parts might have a PartID, PartType, and a few other fields. A related table of PartAttributes might have

[pre]
PartID Attribute AttributeValue
1 Covering Fabric
1 Colour Brown
1 Position Front Right[/pre]

Each attribute of each part would create a new record in a table. You could create a table that identifies the attributes common to specific part types. This solution would be more difficult to implement but would allow you to add attributes on the fly without changing table structures, forms, reports, or queries.


Duane
Hook'D on Access
MS Access MVP

 

[toyden]

@dhookom
sorry about the naming convention i generally use the _ instead of spaces but i have noticed that in my lack of sleep i have missed a few ill correct that. i have fixed the spelling as well i should have waited to get started when my full attention was able to be given.

i like your thoughts on the Attribute table is there a way that i can pick you brain on skype or by email, as this will be only my second database ever i know its a bit ambitus but i learn best by jumping in the deep end and doing the sink or swim method LOL.

@MajP

thanks i will remove the extras now

 

[dhookom]

toyden,

I've got a day job so attempting to walk you through the process of implementing a normalized structure isn't possible. PHV provided a link to information about normalization. I'll gladly answer questions as I have time.

You might want to check out Inventory database structure when inventory items have varying attributes.

Duane
Hook'D on Access
MS Access MVP

 

[toyden]

ok new question the vin number im told could be alpha-numeric can i still use it as a PKey?

 

[MajP]

It is common to use string PKs. Vin has all the properties of a good PK: unique, guaranteed, non changing. But you can always use an auto number.

 

[toyden]

it currently is an auto number field so if i change it to string if would be fine.

 

[MajP]

A VIN is a 17 digit alpha numeric string. It has a very specific format containing lots of information about where and when the vehicle was made. I wrote this vb implementation of the common algorithm for checking if an entered VIN is valid. May come in useful when entering the VIN, you probably want to validate it.

Public Function isVIN(strVIN As String) As Boolean
 Dim I As Integer
 Dim intCount As Integer
 Dim intCount2 As Integer
 Dim aModelYears() As Variant
 Dim aWeights() As Variant
 Dim aCharacters() As Variant
 Dim aCharacterValues() As Variant
 Dim aCheckDigits() As Variant
 Dim aVIN_Array(0 To 15) As Variant
 Dim intTotal As Integer
 Dim intRemainder As Integer
' Check VIN lenth
If Not Len(strVIN) = 17 Then
  MsgBox "ERROR - VIN lenth must be 17 characters long." & Chr(13) & "You only entered " & Len(strVIN) & " characters."
  Exit Function
End If
'make VIN all caps
strVIN = UCase(strVIN)
' model years 1980 - 2000
aModelYears = Array("A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S", "T", "V", "W", "X", "Y")
' weights for multiplyer
aWeights = Array("8", "7", "6", "5", "4", "3", "2", "10", "9", "8", "7", "6", "5", "4", "3", "2")
'characters
aCharacters = Array("A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
'// character values
aCharacterValues = Array("1", "2", "3", "4", "5", "6", "7", "8", "1", "2", "3", "4", "5", "7", "9", "2", "3", "4", "5", "6", "7", "8", "9", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
' check digit 0 - 9 and 10 = X
aCheckDigits = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "X")
'// push each character of the vin into an array removing the 9th character (check digit)
MsgBox "Your VIN's check digit is " & Mid(strVIN, 9, 1)
For intCount = 0 To 16
  If intCount < 8 Then
    aVIN_Array(intCount) = Mid(strVIN, intCount + 1, 1)
  ElseIf intCount > 8 Then
    aVIN_Array(intCount - 1) = Mid(strVIN, intCount + 1, 1)
  End If
Next intCount
'replace with char values
For intCount = 0 To 15
  For intCount2 = 0 To UBound(aCharacters)
    If aVIN_Array(intCount) = aCharacters(intCount2) Then
      aVIN_Array(intCount) = aCharacterValues(intCount2)
    End If
  Next intCount2
Next intCount
'For I = 0 To UBound(aVIN_Array)
'  Debug.Print aVIN_Array(I)
'Next I
'// preform the math
For intCount = 0 To 15
  intTotal = intTotal + aWeights(intCount) * aVIN_Array(intCount)
Next intCount
'debug.print intTotal
intRemainder = intTotal Mod 11
'Debug.Print intRemainder
  If Not Mid(strVIN, 9, 1) = aCheckDigits(intRemainder) Then
    MsgBox "ERROR - Check digit does not compute. Recheck your VIN number." _
          & " Computed check digit:" & aCheckDigits(intRemainder) _
          & " Your check digit: " & Mid(strVIN, 9, 1)
  Else
    MsgBox "Computed check digit: " & aCheckDigits(intRemainder) _
           & " VIN number seems to be valid."
    isVIN = True
  End If

End Function

 

[toyden]

thanks that will be an awesome idea i just need to add this to the form for cars incoming yes??

 

[MajP]

Actually if you spend some time with a few data tables and some vba, you should be able to enter a vin and prepopulate things like
year, make, model, where produced, options,... Could save a lot of work.

http://researchmaniacs.com/VIN/VIN-Decoder.html

 

[toyden]

Hi Majp

thanks for your help so far. would you be willing to help me through this process as i am a newbie to vba and am in need of a good teacher. im happy to build the data tables thats no issue, but the vba i will need lots of help.