locked
Developing database table RRS feed

  • Question

  • Hello everyone,

    I am currently extending my database addressbook. This has been made before, but it is a good practise.

    Current table -> primary key > column, column

    Person -> PersonID > Name, etc.,  AddressID

    Address -> AddressID > Address, etc.

    Profile -> ProfileID > PersonID, social media accounts, etc.

    The columns aint that much important, but the way how I linked it does. So I was wondering if the above way is the correct way to set up a table. A person can have 1 address. And 1 address can be applied to multiple persons. A profile can be linked with 1 person. Now I want to make another table: Family.

    So assume I want to record which people live on the same address. I have already a table address and that table is for addressess only. So a 'family' table. I dont know if the naming is correct, but it should contain all personID records that have the same AddressID. Second thought: I know already who is living on the same addrress... Hmmm...

    Oke, well I still want be able to have a kind of family name. Like with sending e-mails or postcards:

    Fam. Robins  or Fam. Robins and the kids.... etc. So where should I record the family name. Maybe like this:

    Family -> AddressID > familyname

    Any tips or advice on this matter... please tell me!

     

    Greetings,

     

    Spacelama

     

    • Moved by Lisa Zhu Tuesday, May 8, 2012 8:58 AM thread about db design (From:Visual C# General)
    Wednesday, May 2, 2012 6:42 PM

Answers

  • ...A person can have 1 address. And 1 address can be applied to multiple persons...

    If you think it this way, then you dont actually need an Address table. You can only create a new column in Person`s table. But there is a chance that one person CAN have multiple (or at least two) addresses. in this case then you need a new table (like you created it).

    Simple to think when you need a new Table: If there is a chance of n:m (so one person can have multiple addresses, and one address can have multiple people, then you need a tables for each of them).

    As said, if you think one person can live only on one address, then there is just no need of Address table. And as said, create just a new field (column) in Person`s table.

    You always have to think it very logically. Then you can find the correct data structure fast.


    Mitja

    Wednesday, May 2, 2012 7:18 PM
  • I see a number of replies that say get rid of Address table. I wouldn't - you have the relationship correct i.e. there can be many people at one address. If all the people at an address belong to the same 'Family' then there is no need for a Family table. But if more than one family can share the same address then perhaps there is. In that case though a person would have an address ID and a family ID, and the family table would need an address ID.

    One tip: use pencil and paper to sketch relationships. Join tables with lines and put a 'crows foot' at the many end. The time doing this is usually more than recovered since it saves messing around with altering tables in db and code that manipulates them.


    Regards David R
    ---------------------------------------------------------------
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones.
    Object-oriented programming offers a sustainable way to write spaghetti code. - Paul Graham.
    Every program eventually becomes rococo, and then rubble. - Alan Perlis
    The only valid measurement of code quality: WTFs/minute.

    Thursday, May 3, 2012 10:16 AM
  • Hmm. There's a change to the person-address relationship. I thought, one address can have many persons; you now have one person can have many addresses. Is it that the relation is many:many i.e. an address can have many persons and a person can have many addresses? That would usually mean having a linking table (called e.g. PersonAddress) with two columns (address ID and person ID). That would allow two one:many relationships (Person-PersonAddress and Address-PersonAddress).

    However the introduction of Family raises the possibility that the relations should be:

       Person to Family - one:many (Family can have many persons).

       Family to Address - one:one (Family lives in one house).

    Then no need for the Person to Address relationship. However, if a person can have more than one address that needs a rethink.


    Regards David R
    ---------------------------------------------------------------
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones.
    Object-oriented programming offers a sustainable way to write spaghetti code. - Paul Graham.
    Every program eventually becomes rococo, and then rubble. - Alan Perlis
    The only valid measurement of code quality: WTFs/minute.

    Thursday, May 3, 2012 12:40 PM

All replies

  • Hi Spacelama

    to get faster answer to your question You better post in Database design forum

    http://social.msdn.microsoft.com/Forums/en-US/databasedesign/threads


    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Wednesday, May 2, 2012 6:53 PM
  • See Microsoft's pubs database

    John Grove, Senior Software Engineer http://www.digitizedschematic.com/

    Wednesday, May 2, 2012 6:55 PM
  • ...A person can have 1 address. And 1 address can be applied to multiple persons...

    If you think it this way, then you dont actually need an Address table. You can only create a new column in Person`s table. But there is a chance that one person CAN have multiple (or at least two) addresses. in this case then you need a new table (like you created it).

    Simple to think when you need a new Table: If there is a chance of n:m (so one person can have multiple addresses, and one address can have multiple people, then you need a tables for each of them).

    As said, if you think one person can live only on one address, then there is just no need of Address table. And as said, create just a new field (column) in Person`s table.

    You always have to think it very logically. Then you can find the correct data structure fast.


    Mitja

    Wednesday, May 2, 2012 7:18 PM
  • As said, if you think one person can live only on one address, then there is just no need of Address table. And as said, create just a new field (column) in Person`s table.

    My way I create one time an address and apply the ID to the person. Your way you have multiple times the address saved. Isnt that space consuming?

    Let's say I want to have the structure u are talking about: one person can have multiple addressess..

    Then I need a Person Table and a Address table. The Address Table refers back to which person the address has been applied to. right?

    Wednesday, May 2, 2012 7:30 PM
  • You have:

    Table Address: AddressID, Street, HouseNumber, City, PostalCode

    Table Person: PersonID, AddressID_FK, Firstname, LastName, Born, ...

    So you can see a table Person has a foreigh key of AddressID from Address table. And as you figured it out, table Address refers back to exact person.


    Mitja

    Thursday, May 3, 2012 7:02 AM
  • SpaceLama,

    Have you ever heard of a term called over-normalisation, but this will depend on your application in terms of size, grouping address together with an ID would mean that when a new person comes in you need to search if the address exist and then link them to the address by the ID and this is a very costly operation especially if your app is going to be web app.

    What I have see in my experience is that we tend to save address within person's table (you can have work,home,communication etc... Address) and then to find people who work/live together, then you use a search term like postCode, or PO Box in some countries as such the person will have the ID and their address linked.

    As to the social media you can have fields for the main SM (like Twitter,Facebook et...) in the person's table and users will have the option to update their acc with the current usernames.

    I hope this will help


    JacquesM

    Thursday, May 3, 2012 8:58 AM
  • I see a number of replies that say get rid of Address table. I wouldn't - you have the relationship correct i.e. there can be many people at one address. If all the people at an address belong to the same 'Family' then there is no need for a Family table. But if more than one family can share the same address then perhaps there is. In that case though a person would have an address ID and a family ID, and the family table would need an address ID.

    One tip: use pencil and paper to sketch relationships. Join tables with lines and put a 'crows foot' at the many end. The time doing this is usually more than recovered since it saves messing around with altering tables in db and code that manipulates them.


    Regards David R
    ---------------------------------------------------------------
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones.
    Object-oriented programming offers a sustainable way to write spaghetti code. - Paul Graham.
    Every program eventually becomes rococo, and then rubble. - Alan Perlis
    The only valid measurement of code quality: WTFs/minute.

    Thursday, May 3, 2012 10:16 AM
  • Thanks for all the replies. I believe I need to do it like this:

    Person -> personID - name, etc.,

    Address -> AddressID - address, etc, personID <- one person can have multiple addressess

    Profile -> ProfileID - socialmedia accounts, etc., PersonID - <- 1 person 1 profile (Possible multiple profiles. Should I put the ProfileID in table Person?)

    Family name -> familyID - AddressID - Naming: like Family Jordon. or Family Jordon - Smits (for postcards) (1 address should have 1 family name)

    Greetings,

    Spacelama

    @Riced: Thanks for the advice.

    @JacquesM: Your way doesnt allow multiple addressess... If I understood correctly.

    Thursday, May 3, 2012 12:25 PM
  • Hmm. There's a change to the person-address relationship. I thought, one address can have many persons; you now have one person can have many addresses. Is it that the relation is many:many i.e. an address can have many persons and a person can have many addresses? That would usually mean having a linking table (called e.g. PersonAddress) with two columns (address ID and person ID). That would allow two one:many relationships (Person-PersonAddress and Address-PersonAddress).

    However the introduction of Family raises the possibility that the relations should be:

       Person to Family - one:many (Family can have many persons).

       Family to Address - one:one (Family lives in one house).

    Then no need for the Person to Address relationship. However, if a person can have more than one address that needs a rethink.


    Regards David R
    ---------------------------------------------------------------
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones.
    Object-oriented programming offers a sustainable way to write spaghetti code. - Paul Graham.
    Every program eventually becomes rococo, and then rubble. - Alan Perlis
    The only valid measurement of code quality: WTFs/minute.

    Thursday, May 3, 2012 12:40 PM
  • Hi SpaceLama,

    My way can accept multiple address but it depends really on what you want to do you can have table Person with

    {ID,name,surname, address1,Postcode,Correspondanceaddress,CorespondancePostCode,....} the other way

    is to have Address Tabble {ID,Address1, PostalCode,AddressTypeID,PersonID,.....} and have addressType table/have this as an enum type in your code.

    But one thing you need to understand is that people change address, and you need to know wh currently live at x address etc... that is why I said that there is a risk of overnormalisation despite the fact that this is a very simple table.

    The best would be to identify the entity that isn't likely to change and take the relation from there really


    JacquesM

    Thursday, May 3, 2012 3:07 PM
  • Hi ,

     This is a question about DataBase Design.You may have more luck getting answers in the Database design forum(http://social.msdn.microsoft.com/Forums/en-US/databasedesign/threads), rather than the C# forums.

    Have a nice day !


    Lisa Zhu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, May 8, 2012 8:58 AM