Guidence on database design

[Tetelestai]

My background VB6 and some SQL - just remember enough to be dangerous.

First time actually using Access to access data. (eg forms and reports)

Using Access 2000

Need some guidence with a database i'm designing on tracking jobs and their shipping dates

I have Table1 with Fields: JobID (PK), SubDivID (PK), Type, ShipID (FK), InvoiceID (FK)

I have Table2 with Fields: ShipID (PK), ShipFloor (PK) Shipper, ShipDate

I made a form that when I enter in JobID and SubDivID it creates the ShipID and InvoiceID from both the JobID & SubDivID and adds a suffex (eg JobID L64; SubDivID PT; becomes L64PT-shp for ShipID)

First question: Is this a good design practice to do this.

Second question: Is this a good way to code what I did: (should i use a command button? other suggestions?) It works but think it can have flaws.

Private Sub JobID_Change()
    strJobID = Me.JobID.Text
    intJobIDb = 1
    intJobIDi = 1
    intJobIDs = 1
End Sub
 
Private Sub ShipID_GotFocus()
    If intJobIDs = 1 And intSubDIDs = 1 Then
        Me.ShipID.Text = strJobID + strSubDivID & "-shp"
        intJobIDs = 0
        intSubDIDs = 0
    End If
End Sub
 
Private Sub SubDivID_Change()
    intChange = 1
End Sub
 
Private Sub SubDivID_LostFocus()
    If intChange = 1 Then
        strSubDivID = Me.SubDivID.Text
        intSubDIDb = 1
        intSubDIDi = 1
        intSubDIDs = 1
        intChange = 0
    End If
 
End Sub

Third question: Can I now add a record in Table2 with the created ShipID. What is the best way to do this?

Thanks for the Help,

[MMcCarthy]

My background VB6 and some SQL - just remember enough to be dangerous.

First time actually using Access to access data. (eg forms and reports)

Using Access 2000

Need some guidence with a database i'm designing on tracking jobs and their shipping dates

I have Table1 with Fields: JobID (PK), SubDivID (PK), Type, ShipID (FK), InvoiceID (FK)

I have Table2 with Fields: ShipID (PK), ShipFloor (PK) Shipper, ShipDate

I'm just going to deal with the first part first. As it will affect the other questions.

ShipID and ShipFloor as a joint primary key in Table2 is not a good idea unless it's done to solve a many to many relationship. You cannot obtain a unique record using ShipID as the foreign key in Table1 you would need to create a relationship to both ShipID and ShipFloor.

I'm assuming the only reason you've don this is that ShipID in table2 is not unique on its own. Solution is to create a new field XXXID (Type Autonumber) and set it as the primary key and use it as the foreign key in Table 1.

[Tetelestai]

I'm just going to deal with the first part first. As it will affect the other questions.

ShipID and ShipFloor as a joint primary key in Table2 is not a good idea unless it's done to solve a many to many relationship. You cannot obtain a unique record using ShipID as the foreign key in Table1 you would need to create a relationship to both ShipID and ShipFloor.

I'm assuming the only reason you've don this is that ShipID in table2 is not unique on its own. Solution is to create a new field XXXID (Type Autonumber) and set it as the primary key and use it as the foreign key in Table 1.

Thanks for the reply...

I think I'm forming this database wrong... I have the shipping info between the parts of a job

A job (Order of wall panels for a house) may have many floors but one floor belongs to only one job. Each floor can be shipped individually.

I have a tendency to avoid the autonumber cause I believe one of my VB instructors advised against it's use. Is there any concern with autonumber?

Thanks again

[MMcCarthy]

I will address the issues separately.

I have a tendency to avoid the autonumber cause I believe one of my VB instructors advised against it's use. Is there any concern with autonumber?

There may be issues with straight VB but with Access it's almost always a necessity. I'm not sure what your instructors problem with it was but think of primary keys this way.

If you have a natural numeric value that is unique to records your table then this can be used as a primary key without any problems.

If you have a natural alpha/numeric value that is unique to records your table then this can be used as a primary key but it limits functionality as you cannot for example use Max(ID_Field) to get the last entry.

If you have two values that combined make up a unique record you can create a double primary key based on both values. This can cause problems because both values have to be referenced when a used as a foreign key in another table.

There are two ways to create your own primary key.
1. The system Autonumber facility. Access generates the next number based on the last number it issued to this table plus one. Your instructor may have been talking about the numbers jumping sequence because records have been deleted. This is easily resolved by regularly running a compact and repair on your database which should be done for other reasons also.

2. You can create a unique ID using VBA code behind the data entry form. This would mean that records could only be generated using this form. If you want this option I can provide some sample code.

Although, you can create other indexes in a table remember that the Primary Key is the main index and is also used to create relationships between the tables.

[MMcCarthy]

I think I'm forming this database wrong... I have the shipping info between the parts of a job

A job (Order of wall panels for a house) may have many floors but one floor belongs to only one job. Each floor can be shipped individually.

Is this your setup:

Orders have Many Jobs and Jobs have only one order.

This is a many to one relationship

Table Jobs should have a foreign key referencing the OrderID (PK) of Table Orders.

There is one floor per job and therefore this info can be in the Job table

There is one and only one shipment per job
or
A shipment can be made up of one or many jobs

Which is true?

[Tetelestai]

Is this your setup:

Orders have Many Jobs and Jobs have only one order.

This is a many to one relationship

Table Jobs should have a foreign key referencing the OrderID (PK) of Table Orders.

There is one floor per job and therefore this info can be in the Job table

There is one and only one shipment per job
or
A shipment can be made up of one or many jobs

Which is true?

I would say an order is the same as a job - A start order has only one job

a job could have many floors

each floor may be shipped at different times

[MMcCarthy]

I would say an order is the same as a job - A start order has only one job

So you should have an orders table containing details about jobs.

a job could have many floors

So you should have an orders_details table with a new record for each floor and a foreign key field to the OrderID (PK) of the orders table and a foreign key field to the ShipID (PK) of the shipments table (see below) .

each floor may be shipped at different times

So you have a shipments table with details of shipments. See above.

Now you have a relationship orders --> orders_details --> shipments

Starting to get the picture?

Mary

[Tetelestai]

So you should have an orders table containing details about jobs.



So you should have an orders_details table with a new record for each floor and a foreign key field to the OrderID (PK) of the orders table and a foreign key field to the ShipID (PK) of the shipments table (see below) .



So you have a shipments table with details of shipments. See above.

Now you have a relationship orders --> orders_details --> shipments

Starting to get the picture?

Mary

Starting, I will have to playaround with this for abit (might be awhile, other work calling)

Thanks

[MMcCarthy]

Starting, I will have to playaround with this for abit (might be awhile, other work calling)

Thanks

No problem

It's important to get your head around this before proceding further.

Take your time.