By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Guidence on database design

P: 34
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.


Expand|Select|Wrap|Line Numbers
  1. Private Sub JobID_Change()
  2.     strJobID = Me.JobID.Text
  3.     intJobIDb = 1
  4.     intJobIDi = 1
  5.     intJobIDs = 1
  6. End Sub
  7.  
  8. Private Sub ShipID_GotFocus()
  9.     If intJobIDs = 1 And intSubDIDs = 1 Then
  10.         Me.ShipID.Text = strJobID + strSubDivID & "-shp"
  11.         intJobIDs = 0
  12.         intSubDIDs = 0
  13.     End If
  14. End Sub
  15.  
  16. Private Sub SubDivID_Change()
  17.     intChange = 1
  18. End Sub
  19.  
  20. Private Sub SubDivID_LostFocus()
  21.     If intChange = 1 Then
  22.         strSubDivID = Me.SubDivID.Text
  23.         intSubDIDb = 1
  24.         intSubDIDi = 1
  25.         intSubDIDs = 1
  26.         intChange = 0
  27.     End If
  28.  
  29. 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,
Nov 20 '06 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 20 '06 #2

P: 34
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
Nov 21 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 21 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534

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?
Nov 21 '06 #5

P: 34
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
Nov 21 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 21 '06 #7

P: 34
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
Nov 21 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 21 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.