Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313

    Unanswered: One to One Design?

    Hi all,

    I've never had reason to use a 2 tables with one to one relationship. Just wondering the pros and cons to using it in this situation.
    I currently have a job tracking db and need to handle estimates. The details of the estimates are just line items and estimate charges, whereas the details of the job ticket are quite complex (time, materials, etc.)
    The relationship between an estimate and a job ticket would be one to one. My question is, do design rules dictate keeping them in the same table?

    So, could be tblEstimate, tblEstimateDetail, tblJobTicket, tblJobTicketDetail
    OR tblJobTicketandEstimate, tblEstimateDetail, tblJobticketDetail

    Edit: An Estimate may or may not become a live Job Ticket

    Thanks!

    Chris
    Last edited by cpgospi; 02-24-05 at 13:42.

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    A relationship in a database always carries an overhead when you are joining tables together in queries and in maintenance for the integrity of the table.

    You should have separate tables to normalise the database, but if there is no design requirments not to, try to keep the data together in one table. The example always given for a one-one relationship is for an employees table where some fields are of a sensitive nature.

    Unless you have a very compelling reason to I would suggest that you are creating unnecessary work for yourself by splitting a table into a one-one relationship. Use queries and VBA to work with different columns in the table.
    Justin

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by cpgospi
    Hi all,

    I've never had reason to use a 2 tables with one to one relationship. Just wondering the pros and cons to using it in this situation.
    I currently have a job tracking db and need to handle estimates. The details of the estimates are just line items and estimate charges, whereas the details of the job ticket are quite complex (time, materials, etc.)
    The relationship between an estimate and a job ticket would be one to one. My question is, do design rules dictate keeping them in the same table?

    So, could be tblEstimate, tblEstimateDetail, tblJobTicket, tblJobTicketDetail
    OR tblJobTicketandEstimate, tblEstimateDetail, tblJobticketDetail

    Edit: An Estimate may or may not become a live Job Ticket

    Thanks!

    Chris
    No. Only their relative importance to the primary key does ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Thanks guys, one table it is.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by cpgospi
    Thanks guys, one table it is.
    Well don't be too HASTY ... Keep in mind the Access limit of 255 columns per table ... Keep in mind: Do you want to maintain some data with other data?

    I had at 1 point the same construct: 2 tables lock-step with each other where the 2nd was effectively an extension to the 1st (Job and Job accumulators) I went with the 2 table solution to separate the columns because they relate to the job but do not describe the job (plus I wanted only 1 row to tell me how many hours were worked and how many purchases were purchased)...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Not as straightforward as I thought. It's possible that it may approach the 255 limit in the future. I'd rather avoid that situation. Won't be too much work to use 2 tables. Have to give it some more thought.

    Thanks again.

  7. #7
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    I would suggest that if you have a table that requires 255 columns (or even 50) then you need to look at the design of your database and make sure that that is not the problem

    Justin

  8. #8
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Thanks Justin. Agreed. I will probably create another post soon with the ER screen attached. I don't think the design is terrible, but it's definately not as efficient as it could be. I'm sure there's better ways of handling some of the things I've done, but I've tried to closely follow normalization rules (which could be my prob).
    Anyway thanks again.

  9. #9
    Join Date
    Mar 2004
    Posts
    29
    Is this really as straight forward as a 1 to 1 relationship.

    While I understand why you would have a 1 to 1 relationship between a successfull estimate (or bid) and work done.

    Would your client for instance make multiple estimates to obtain the same contract, and if so would they want to track that for the future?

    Estimate1 - $250
    Estimate2 - $200 - accepted --> JobDetail

    Therefore your estimates could create childless parent records. This will be impossible to track if you normalize into one table. Just a thought.

  10. #10
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Thanks Coldedus. I'm going back to the drawing board on this one. There are some other design flaws in my DB that I've been able to code around...but they're starting to fester now. I'm the entire IT Dept at a small company, so I'm responsible for it working or not. I don't know how long I will be supporting this and I don't want to be one of those "left a piece of sh* DB behind" guys.
    Got the OK to invest the time to get it right, so I'm gonna kick it around this weekend and post some design questions next week.
    Thanks again.

  11. #11
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Have a look at http://support.microsoft.com/default...b;EN-US;289533 for some pointers on how to design databases

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •