Results 1 to 14 of 14
  1. #1
    Join Date
    May 2007
    Posts
    10

    Unanswered: Database Design Question

    Question 1:

    I have a contacts table which holds different types of contacts. I contact can only have 1 type. I have a contact types table and store the id of the contact type in the contacts table. I also have another table (table 1 for example) that has a 1 to many relationship with a contact. A contact can have 1 or many of table 1. But only a certain type of contact. Not all contact types have this relationship with table 1. They aren't related to table 1 at all. Is this ok, and how is it represented in a database diagram?

    Question 2:

    I have a contacts table and an address table and a contact can have 1 or many addresses. I also have a vendors table. They will also have addresses. I want to share the address table with contacts and vendors. Would I just have an address id as a primary key. Use the contact id or vendor id (depending on what type the address is for) as a foreign key, and then also have another column in the address table that held lets say a 0 or 1 depending on whether or not it was a contact address or a vendor address? Would this be the proper way to do this?

    Any suggestions are welcome and thank you in advance!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cartch
    Question 1:
    Is this ok, and how is it represented in a database diagram?
    most definitely yes, it is okay

    diagram? diagram?

    (you have to imagine jim mora saying "playoffs?")

    is this a homework assignment?

    Quote Originally Posted by cartch
    Question 2:
    Would I just have an address id as a primary key.
    yes

    the other part is trickier

    you could, for example, have both a contact id and a vendor id foreign key in the address, both of which must be nullable, since one of them (the one it isn't an address for ) will be null on every row

    i personally would not have a 0 or 1 flag for whether or not it was a contact address or a vendor address
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2007
    Posts
    10
    lol...no..not a homework assignment. Just trying to be very organized on this project! Thanks for your reply. Would you recommend sharing the address table, or, would you have a separate address table for contact and vendor? I would think it makes sense to have only 1 address table.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cartch
    I would think it makes sense to have only 1 address table.
    okay, let's go with that for a second

    why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2007
    Posts
    10
    why what...have a diagram, or have 1 address table?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why does it "make sense" to have only one address table? how is it better than two?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2007
    Posts
    10
    less duplication? Can I run another design by you? I'm really not doing homework...it's been a long time since I've done any database design so I'm rusty.

    I have this situation.

    A person can hold a season ticket for 1 or more teams. A team has more than 1 game and a game has more than 1 ticket. Here is how I'm designing my DB (not, only including necessary fields right now) Does it make sense to you?

    Table:
    tblTeam
    Field(s):
    TeamID PK

    Table:
    tblContacts
    Field(s):
    ContactID PK
    (note: I will be using the contacts table for people other than season ticket holders as well)

    Table:
    tblSeasonTicket
    Fields(s):
    SeasonTicketID PK
    TeamID FK
    ContactID FK

    Table:
    tblGames
    Field(s):
    GameID PK

    Table:
    tblTickets
    Field(s):
    TicketID PK
    SeasonTicketID FK
    GameID FK


    Appreciate your thoughts if you're willing to give them.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    less duplication? i don't think so

    how often will a vendor and a contact share an address?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2007
    Posts
    10
    sorry...what I meant was less tables...I'm going to have the same fields for an address. instead of creating a new table, I would only have to add a field. I actually prefer your way with the two tables...I find that easier to keep track of.

  10. #10
    Join Date
    May 2007
    Posts
    10
    you can bill me for it if you want

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, my answers on public discussion forums are always free
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    May 2007
    Posts
    10
    great...do you have a comment about my ticket scenario? :-)

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is it possible to get a ticket for a game that isn't a season ticket?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    May 2007
    Posts
    10
    no, it is not possible to get a ticket for a game that isn't a season ticket

Posting Permissions

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