Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Posts
    7

    Question Table Design Help

    I'd like to ask for an opinion of some more experience db admins. I am a programmer, but tasked to design a database schema. I have some normalization experience, but to be honest, the size of the application requires some insight that I might not have.

    So basically, I have some tables in which I will have to represent what are called users and providers. For both of these, I will need to store their addresses. I want to know if it is a more scalable design to have one address table for both or to have an address table for each. For example, look at the to contrasting table designs below:

    ----- design 1 -----

    So far I have:

    users {
    id
    name
    ...
    }

    providers {
    id
    name
    ...
    }

    addresses {
    id
    line1
    line2
    ...
    }

    user_addresses {
    id
    user_id
    address_id
    }

    provider_addresses {
    id
    provider_id
    address_id
    }

    ----- design 2 -----

    users {
    id
    name
    ...
    }

    user_addresses {
    id
    user_id
    line1
    line2
    ...
    }

    provider_addresses {
    id
    provider_id
    line1
    line2
    ...
    }

    ----- design 3 -----

    or is there a better way to handle all this?

    My thoughts that were design 1 is more normalized. Design 2 seems to be faster in terms of query speed.

    Can you please compare and contrast these two designs?

    -------------------------

    Thanks in Advance!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    As a general rule, I prefer one of anything to multiple. Based on that preference, I'd have one address table and use its Pk to link addresses to other tables. This helps enormously when you need to do address changes because they are all in one table, stored one way, and it also allows you to create a single routine to handle addresses within your code, which can then handle every address needed in your system!

    -PatP

  3. #3
    Join Date
    Jun 2003
    Posts
    7
    Thanks for the feedback. I personally do like design 1 better. There is a possibilty that I will be storing other types of address also. In that case, would it be okay to keep adding more lookup tables like user_addresses and provider_addresses? For example:

    vendor_addresses {
    id
    vendor_id
    address_id
    ...
    }

    Would it be okay for normalization to stop here (in terms of having many types of entities that addresses are related to)? I'm sure I could go further, but would it be worth the effort?

    Thanks again....

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Based on what you've described so far, your plan sounds fine to me. You can get crazy with normalization, but it sounds like you've got a good handle on the basic concepts.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Posts
    7
    Thanks for your input!

  6. #6
    Join Date
    Dec 2004
    Posts
    54

    How Could You????

    Dear Sir,
    User and Provider? Look at the attribution. A user and provider has the same attribution. What does this tell you?

    They are the same

    HHhhmm a user and a provider are BOTH people playing a different role

    Could a user ever be a provider? Could a proivder ever be a user?

    A person can play many roles. Please save yourself tons of work, because your business partners will surely come up with another role in the future.

    Why store the exact same data - data for a 'person' - more than once just because a person plays two different roles

    Create a person table
    Create a role table
    When a person enters into a relationship.... then drag them and the role they're in together.

    :-)
    Vmusic

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    If you start from the assumption that ecastanedo actually enumerated all of the attributes for both user and provider, then your conclusion makes sense. I can't remember a case where all of the attributes were enumerated in a posting like this, but I suppose that it could happen.

    If ecastanedo has posted the DDL from a DMO generated script, I'd reach exactly the same conclusion you did, at least as fast as you did. Since this list was hand-typed, I made the assumption that it had been generalized for brevity, and I should have stated my assumption.

    -PatP

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    design 1 is for many-to-many

    design 2 is for one-to-many

    i would suggest that neither is "better" except insofar as it models the real-world relationships that you're trying to model

    for example, if two users can have the same address (husband and wife, for example), then both designs can accommodate this, but in design 2 you will have the same address on two rows

    does this redundancy matter? i would suggest that in a normal sample of users, you will find shared addresses, what, maybe 3% of the time? only you can determine this

    so i would suggest that the savings in storage space are immaterial

    which design is better? well, a lot depends on the sql needed to maintain the data and retrieve the data, doesn't it

    how often are you going to run a query that sends only one piece of mail to one address? this requires identifying the redundant address in design 2, but in design 1, it requires deciding which person in a shared address to pick

    the only thing i can suggest as an improvement is that in both design 1 and design 2, you do not need the surrogate id columns in either the user_addresses or provider_addresses table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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