bigdspbandj Posted October 3, 2007 Share Posted October 3, 2007 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! Quote Link to comment https://forums.phpfreaks.com/topic/71724-solved-db-design-input-needed/ Share on other sites More sharing options...
HuggieBear Posted October 4, 2007 Share Posted October 4, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/71724-solved-db-design-input-needed/#findComment-361526 Share on other sites More sharing options...
MmmVomit Posted October 4, 2007 Share Posted October 4, 2007 Do you want to store every status change for a job? In other words, do you want to be able to see that Job42 has the following history received, 1/1/01 proofed, 1/3/01 approved, 1/9/01 Or do you just want to store the current status of the job? Quote Link to comment https://forums.phpfreaks.com/topic/71724-solved-db-design-input-needed/#findComment-361771 Share on other sites More sharing options...
bigdspbandj Posted October 4, 2007 Author Share Posted October 4, 2007 Exactly, MmmVomit. I figure to get the current status I can just do a date sort and grab the latest status for that particular job. Quote Link to comment https://forums.phpfreaks.com/topic/71724-solved-db-design-input-needed/#findComment-361936 Share on other sites More sharing options...
MmmVomit Posted October 4, 2007 Share Posted October 4, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/71724-solved-db-design-input-needed/#findComment-362004 Share on other sites More sharing options...
Barand Posted October 7, 2007 Share Posted October 7, 2007 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. Don't see why, looks good to me and I guess Mmmvomit is agreeing too, in a roundabout way Quote Link to comment https://forums.phpfreaks.com/topic/71724-solved-db-design-input-needed/#findComment-364189 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.