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?
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!
Would I just have an address id as a primary key.
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
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.
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?
(note: I will be using the contacts table for people other than season ticket holders as well)
Appreciate your thoughts if you're willing to give them.
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.