locked
A question about designing relationships RRS feed

  • Question

  • I have a database design where I'm struggling to implement some table relationships.

    One of the tables has to refer to a table that contains addresses, but it needs to refer to the addresses in a specific way. I need two addresses and they exist for very different reasons, so I can't use a 1:M relationship because I can't then tell the difference between the two addresses. I can't extend the addresses table in order to store some sort of differentiator because the address table is used elsewhere.

    What is the best way for me to define this, not just in terms of storage in the tables but also in terms of the relationship? I'm a real novice at using SQL Server Management Studio and, so far, all I've been able to define is 1:M relationships.

    Ask questions if I haven't provided enough information or clarity ;-)

    Thanks.

    Philip

    Wednesday, February 15, 2012 4:51 PM

Answers

  • Here's where i'm getting stuck. :)

    - customer can have multiple billing addresses and multiple shipping addresses.
    - supplier can have multiple addresses

    -So, based on the previous discussion, there should be a column in the CUSTOMER table for the billing addresses and a separate column in the CUSTOMER table for the shipping addresses. There would be a single column in the SUPPLIER table.

    If a customer has one billing address and one shipping address, there should be one COLUMN for each in the Customer TABLE:

    Customer
    ------------
    Id
    Name
    Address_Billing
    Address_Shipping

    And similarly for the supplier address.

    If they may have multiple billing addresses, and multiple shipping addresses, a child TABLE is used, as Sandeep suggested:

    Customer_Address_Shipping
    -------------------------------------
    Customer (FK Customer)
    Address (FK Address)

    Customer_Address_Billing
    -------------------------------------
    Customer (FK Customer)
    Address (FK Address)

    And, again, same for supplier.

    Of course, this only matters if the same address is used twice. Otherwise, the address itself does not require a separate TABLE.

    • Marked as answer by Philip Colmer Thursday, February 16, 2012 5:13 PM
    Thursday, February 16, 2012 1:30 PM
    Answerer

All replies

  • If the two references are for two different reasons, it is irrelevant that they are both addresses. As far as the first TABLE is concerned, they are two diffeent elements.

    Hence, the first TABLE should get another COLUMN to refer to the second address.

    • Edited by Brian TkatchEditor Wednesday, February 15, 2012 6:26 PM
    • Proposed as answer by SandeepM_ Thursday, February 16, 2012 11:43 AM
    Wednesday, February 15, 2012 6:25 PM
    Answerer
  • I understand what you've explained but - and it may be that I'm doing something wrong in SQL Server Management Studio - I'm not explicitly adding columns for each reference. Instead, I'm creating relationships between the tables. So, the address table (in this example) has a column for each table that needs to store addresses. I then create a 1:M relationship between the appropriate column in the address table to the ID column in the table that needs an address.

    The reason I've done it this way is because I'm using a tool that takes the database design & creates the code necessary to use the LINQ to SQL functionality in Windows Phone.

    But I realise that I'm probably being hampered by my lack of knowledge and understanding here.

    Philip

    Wednesday, February 15, 2012 6:36 PM
  • Don't know.

    This SQL would be something like:

    ALTER TABLE <tab1> ADD <new_col> <data_type> REFERENCES <address_tab>;


    Wednesday, February 15, 2012 7:39 PM
    Answerer
  • I'm going to rephrase the issue I'm facing because I don't think I've actually got the fundamentals right in designing the relationships between the tables.

    So let's start again.

    Supposing I have a CUSTOMER table, a SUPPLIER table and an ADDRESS table. A customer can have multiple billing addresses and multiple shipping addresses. Similarly, the supplier can have multiple addresses but I don't need to know the difference between them. So, based on the previous discussion, there should be a column in the CUSTOMER table for the billing addresses and a separate column in the CUSTOMER table for the shipping addresses. There would be a single column in the SUPPLIER table.

    However, and this is probably where I'm going wrong, my tables aren't designed this way. Instead, the ADDRESS table has its primary key (ID) and then two foreign keys - one for customers (CustomerID) and one for suppliers (SupplierID). Neither the CUSTOMER table nor the SUPPLIER table have any columns referring to the ADDRESS table. Instead, the 1:M relationship between the ADDRESS table and the SUPPLIER table uses the primary key in the SUPPLIER table.

    So that is where I'm getting stuck. The ADDRESS table needs to have a foreign key (the Customer ID) in order to refer back to the CUSTOMER, but there isn't any reference in the CUSTOMER table to the ADDRESS table. Is this because I'm designing my relationships "backwards"?

    Should I be storing the AddressID in two different columns in the CUSTOMER table as the foreign key instead? That doesn't seem right to me because the way I've got it designed at the moment is a 1:M relationship so putting the foreign key into the CUSTOMER table would imply that an address could have multiple customers???

    Thanks.

    Philip

    Thursday, February 16, 2012 9:36 AM
  • Hi Philip,

    As per my understanding, you need 1 to M relation model. Also as Brian said, you can define one more address column to represent this too.

    you can also define your model like below also: (one more way)

    Customer and Customer Address

    Supplier and Supplier Address

    You can also add one more table called, Address Type  and keep Address Type ID column in Customer Address or Supplier Address to make sure multiple type of address are maintained. Like (Primary, Secondary, etc..)

    Hope this help.

    Thank you,

    Sandeep.


    - Mishra, Sandeep. (Success is a journey, not a destination.:)


    • Edited by SandeepM_ Thursday, February 16, 2012 11:47 AM
    • Proposed as answer by Brian TkatchEditor Thursday, February 16, 2012 1:22 PM
    Thursday, February 16, 2012 11:42 AM
  • Here's where i'm getting stuck. :)

    - customer can have multiple billing addresses and multiple shipping addresses.
    - supplier can have multiple addresses

    -So, based on the previous discussion, there should be a column in the CUSTOMER table for the billing addresses and a separate column in the CUSTOMER table for the shipping addresses. There would be a single column in the SUPPLIER table.

    If a customer has one billing address and one shipping address, there should be one COLUMN for each in the Customer TABLE:

    Customer
    ------------
    Id
    Name
    Address_Billing
    Address_Shipping

    And similarly for the supplier address.

    If they may have multiple billing addresses, and multiple shipping addresses, a child TABLE is used, as Sandeep suggested:

    Customer_Address_Shipping
    -------------------------------------
    Customer (FK Customer)
    Address (FK Address)

    Customer_Address_Billing
    -------------------------------------
    Customer (FK Customer)
    Address (FK Address)

    And, again, same for supplier.

    Of course, this only matters if the same address is used twice. Otherwise, the address itself does not require a separate TABLE.

    • Marked as answer by Philip Colmer Thursday, February 16, 2012 5:13 PM
    Thursday, February 16, 2012 1:30 PM
    Answerer
  • If a customer has one billing address and one shipping address, there should be one COLUMN for each in the Customer TABLE:

    Customer
    ------------
    Id
    Name
    Address_Billing
    Address_Shipping

    What are Address_Billing and Address_Shipping defined as? Everything I'm reading says that a one-to-many relationship has the foreign key in the child table (in this discussion, the Address table) and that foreign key would be the value of the customer's ID. So I'm still stuck on trying to understand how you would define two one-to-many relationships between the Customer and Address tables. If I have an address that refers to a customer's ID, how do I tell if that is a billing or shipping address?

    (And I can't put a flag into the address table - I'm using this as an example to try to get it straight in my head)

    If there is an article or a book that gives a clear concrete example of how you can have two tables with (say) two one-to-many relationships between them, I'm happy to go away and read it but I haven't found that yet.

    Thank you.

    Philip

    Thursday, February 16, 2012 5:04 PM
  • Customer_Address_Shipping
    -------------------------------------
    Customer (FK Customer)
    Address (FK Address)

    Customer_Address_Billing
    -------------------------------------
    Customer (FK Customer)
    Address (FK Address)


    I've decided that having an intermediary table in order to identify billing vs shipping addresses is the simplest/best way to solve this even though the addresses don't need to be used multiple times.
    Thursday, February 16, 2012 5:14 PM
  • What are Address_Billing and Address_Shipping defined as?

    The data type is the same as the PK for address.

    They are the "many" side of the "one-to-many" relationship.

    >one-to-many relationship has the foreign key in the child table (in this discussion, the Address table)

    The Address is the parent. Customer refers to it, so in regard to their relationship, Customer is the child.

    >If I have an address that refers to a customer's ID, how do I tell if that is a billing or shipping address?(And I can't put a flag into the address table - I'm using this as an example to try to get it straight in my head)

    What does 1+1=?  Don't say 2. :)

    Let's ask a  basic question here. Is any given Address used in more than one place? (So that, changing one by definition changes the other...).
    Thursday, February 16, 2012 5:24 PM
    Answerer
  • Today's convenience is tomorrow's problem. If you have the time, let's get it straight from the beginning.
    Thursday, February 16, 2012 5:25 PM
    Answerer