Jump to content

[SOLVED] DB design: input needed


bigdspbandj

Recommended Posts

I am developing a tracking system for jobs that come in through my website. Each job is tracked by the status I need to store when (a datetime stamp) the job was labeled with each status.

 

The status labels are: received, proofed, approved, printed, inserted, mailed, billed, hold, canceled.

 

I have two tables as of now:

 

status_table:

id

label

description

 

and a job table where other information is stored.

 

Would it be best to have look up or reference table which has the status_id, job_id, date and have a column in the job table "current_status" or to store the dates in columns in the job table such as date_received, date_proofed, date_approved...

 

Any suggestions would be much appreciated. Thanks!

Link to comment
Share on other sites

I'll probably be shot for this, but I think the way I'd do it is to have a job_detail table, a status table, and a job_status table.

 

job_detail

job_id -> unique primary key

All your job details (doesn't include a status column)

 

status

id -> unique primary key

status

 

job_status

job_id -> not unique, but possibly indexed

status_id

timestamp

 

I don't see the point in having a status column in the job_details table as you'd need to update two tables when changing a status.  An insert into one and an update on the other.  I'd rather select from two than update/insert to two.  And because no status column exists in the job_details table, there's no real need to have a unique primary key on the job_status table.

 

I'm open to reasons as to why this isn't a good idea though, I'm no database expert.

 

Regards

Huggie

Link to comment
Share on other sites

Here are some options

 

Job_table
-----------
ID
Lots of irrelevant columns

Job_status
-------------
ID
Job_ID  \\ link to Job_table.ID
Job_Status  \\ string literal
Date

 

This is simple and straight forward.  The problem is, the possible values of Job_Status would have to be hard coded somewhere.

 

Another approach

 

Job_table
-----------
ID
Lots of irrelevant columns

Job_status
-------------
ID
Job_ID  \\ link to Job_table.ID
Job_Status  \\ special ID number
Date

 

This is similar to the first approach.  An ID number is assigned to each status, and it is decoded programatically.  Again, requires some hard coding.

 

Another approach.

 

Job_table
-----------
ID
Lots of irrelevant columns

Job_status
-------------
ID
Job_ID  \\ link to Job_table.ID
Job_Status  \\ link to Job_status_lookup.ID
Date

Job_status_lookup
---------------
ID
Description

 

This is more maintainable, because all you have to do is query Job_status_lookup to populate the dropdown box with the possible values for job status.  If you adopt this approach, though, what will you do with similar situations?  Do you want 100 different value lookup tables scattered around your database?

 

Another approach

 

Job_table
-----------
ID
Lots of irrelevant columns

Job_status
-------------
ID
Job_ID  \\ link to Job_table.ID
Job_Status  \\ link to Table_lookup.ID
Date

Table_lookup
---------------
ID
Table_ID
Description

 

Table_lookup is a generic table that stores ALL such values.  Table_ID stores a value that identifies which table that particular lookup value corresponds to.  Job_status could have a value of 42, so you would lookup the values for the dropdown box by querying Table_lookup where Table_ID = 42.

 

This is the most robust, but can also be complex to implement.  If your application is small enough, you might want to avoid this approach.

 

I work with a large commecial database, and both the second and fourth approaches are used.  Any of the very basic information is encoded using approach 2.  Any information that may be subject to change is done using the fourth approach.  For this instance, I would lean towards the second (hard coded ID numbers) or third (dedicated lookup table) approach.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.