db structure - would like an opinion

Results 1 to 3 of 3

Thread: db structure - would like an opinion

  1. #1
    PJ Guest

    Default db structure - would like an opinion

    Hello. I am wondering if you think there is a particular advantage to doing either of the following:<BR><BR>Scenario 1: <BR>I have a customer table. Every record is unique per customer. It holds their name, contact info, and address.<BR>I have another table that holds account info. Each record is also unique per customer. It holds data such as previous balance, current balance, last payment date, etc. There will never be more than one record per customer. I keep the tables separate as a matter of categorization and clarity.<BR><BR>Scenario 2:<BR>I combine the two tables above into one. Why? Because there&#039s a one-to-one relationship between the tables. There will never be more than one record per customer in either table. Why not have one less table and keep it simple.<BR><BR>My question is about which would be a better way to structure this. I thought it was generally a good practice to keep unrelated data in separate tables. In this case, the nature of the two tables is different (general info vs. billing), but I don&#039t know if it&#039s enough for a second table, especially with the one-to-one relationship. Which should I use?<BR><BR>Can you make an argument for one over the other?

  2. #2
    Join Date
    Dec 1969

    Default RE: db structure - would like an opinion

    It&#039s arguable either way. Without knowing more about the eventual use of the data, I&#039d probably say just use the one table because it&#039s more efficient (you&#039ll end up doing a lot of JOINs otherwise). The ideal of database design is what&#039s called Boyce-Codd Normal Form, which states that "every determinant is a candidate key" (if I remember correctly). In this case the only determinant is the customer id (or whatever else identifies a customer uniquely), which is also the primary key (presumably) so this is in BCNF, I think...<BR><BR>HOWEVER, experience tells me that requirements change. If there is ANY possibility that at some point in the future someone will decide that a customer can have more than one account then I&#039d plan for that now rather than later and use the two table approach.<BR><BR>Clear? No, me neither...<BR><BR>Dunc

  3. #3
    PJ Guest

    Default RE: db structure - would like an opinion

    Thanks for the input. My anal retentive side wants to have separate tables, cuz &#039this goes here&#039 and &#039that goes there&#039. But the efficient/lazy side wants to keep them together. I also like your suggestion about planning for the future, in the event that there may one day be more than one record per customer in either table. That point quickly helped me to decide to keep them separate.<BR><BR>Thanks!

Posting Permissions

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