Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2007
    Posts
    5

    Repeated attributes in table - addresses

    Hi, i have found out as i have developed the database i've ended up
    with a number of tables that have the same attributes/fields in.

    For instance, my suppliers and customers have the same fields for the
    address. Would it make more sense to create an additonal table and to
    reference the addressID in the address table from the customers and suppliers tables?

    Some tables like employees have multiple addresses, like home address,
    next of kin address etc. Now this is more of an issue for maintance as
    if i want to add an additional address fields (such as state) then i
    would need to add that for each table that has address like fields.

    Is this a comon and recommended way? Its not normilsation per se as
    this isnt reducing the data it just makes it easier to maintain.

    Also, how would i go about mixing a table of contacts, which could
    either belong to supplier, customer company etc? A contact wouldnt be
    able to have both a supllier and customerID as one would be blank,
    would i be better off having a CompanyID FK in the contacts table, and
    then in the Companies table a lookup domain value which defines wether
    the company is supplier/cuustomer etc?

    Appreciate any recommendations on good books for the basic design
    scenarios and how they are done professionally. Spent an hour on
    google without too much enlightenment hence the post here.

    Many thanks in advance,

    Chris

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    we've had this discussion on this forum before

    unfortunately it is not easy to find these threads, because people use such illuminating thread titles as "please help me with my ERD" and "database design considerations" and "a data modelling conundrum" which means that as you scroll down the list of previous threads, you don't really know what they're about

    and i will not suggest using this site's search function because it's b0rked

    so perhaps we will need another thread -- note that this one has a nice title

    Quote Originally Posted by g18c
    For instance, my suppliers and customers have the same fields for the
    address. Would it make more sense to create an additonal table and to
    reference the addressID in the address table from the customers and suppliers tables?
    more sense? no, but it is a practical alternative

    Quote Originally Posted by g18c
    Some tables like employees have multiple addresses, like home address,
    next of kin address etc. Now this is more of an issue for maintance as
    if i want to add an additional address fields (such as state) then i
    would need to add that for each table that has address like fields.
    how you can anticipate forgetting the state column is a puzzle

    Quote Originally Posted by g18c
    would i be better off having a CompanyID FK in the contacts table, and
    then in the Companies table a lookup domain value which defines wether
    the company is supplier/cuustomer etc?
    that's certainly a good option

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

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I mysqlf have asked that very same question because it seems like the best way to go. One address table for the entire database, one telephone number table etc. The problem I had was that I took it too far.. I wanted 1 table for names, one table for phone numbers, etc. My model was an absolute nightmare and it was really hard to follow because of all the association tables that were needed.

    If your model is small, I wouldn't think it would be a big deal. When you model your tables like that, you will see the real "fun" begin when you have to join everything back together with all of the association tables that are needed.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I actually created a separate address table in one of the largest databases that I designed from scratch.

    The developers hated that at first because it meant that they had to code one extra join for every address that they needed. They soon discovered that it meant that they could build one "address pannel" and simply pass it the appropriate address_id and get the display/edit of any address out of the way with one line of code.

    For me as a DBA it was even better, since all of the addresses were in one table... Any time we needed to make changes (such as zip code refreshes, mailing route updates, etc) there was only one place to hunt the little beggars down. This was a huge boon to me!

    I see data modeling and object modeling as closely related. If you can factor out a component object from your object model, then I think that you ought to factor it out from your data model too.

    -PatP

  5. #5
    Join Date
    Apr 2007
    Posts
    5
    Ok thanks so much for the replies. On the flip side, i use the CSLA .NET framework for my business objects tier and that, rightly so, has the methodology of modeling the business objects as behavioural and not as derived classes per se. This does make for more repetitive coding (or code generation) as classes will share attributes but will be explicitly defined in each class, but it does mean the structure is well defined and easy to follow at the expense of bloat.

    I think from what people have said i will need to find a middle ground, perhaps i will have the following as it will fit well:

    Address Table:
    AddressID
    Address
    State
    Country
    ...etc

    EmployeesAddresses (PK on all columns)
    CustomerID
    AddressID
    EmployeeAddressType (Home, Work, next of kin)

    SuppliersAddresses (PK on both columns)
    SupplierID
    AddressID

    and so on, basically having the one address table and having independent tables hooking these addresses to the required tables, customers, suppliers, employees etc. Does that sound sensible?

    Really appreciate the help, i know some db design but im more software engineer than DBA! If i can help anyone with business objects or winforms front end code ill be glad to help.

    Cheers,

    Chris

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I think that looks like a great design. I would be curious to see what both r937 and Pat think of it. I have questions of my own I would like to ask.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it works for me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'm rather pragmatic... If it works for Chris, it works for me. The design itself looks quite workable to me.

    I'm hoping that the CustomerID in the EmployeeAddresses was supposed to have been an EmployeeID though!

    -PatP

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Chris,

    What did you decide for the other entities? Did you get them yet?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You have suppliers and customers in separate tables.
    Can a supplier never be a customer, or vice versa?
    You should combine both these tables into a single table called "Businesses", which would eliminate your duplicate address problem, and maybe other problems as well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2007
    Posts
    5
    Thanks for the replies chaps. Yes the CustomerID was a mistake, should be EmployeeID. I havent finished the schema design just yet as i was side tracked with getting a new server installed But the comments are very welcome and will help me to progress as i have new ideas and will post shortly.... i want to keep the ideas rolling as i have had many since people kindly gave me pointers, its been a great help.

    One of these ideas is what blindman has said, yes combining customers and suppliers into a generic 'contacts' table would make sense. However, sometimes different entities will require different fields. For instance, people in my company, suppliers and customers are all people correct? However, for our employees we would want to store passport number, salary etc, additional fields we wouldnt want to store (or we wouldnt even know!) from suppliers or customers... what do we do in theses cases? Do we have the one contacts table and end up with some of the fields as null, or do we have seperate tables with the fields as required in each (hence the rational to abstract out the address table as these would be common between all people entities). For my case, an employee would never be a customer nor a supplier and vice versa. I have seen http://allenbrowne.com/AppHuman.html which would be quite interesting but i am aware of keeping it simple as possible as it will make my business logic and objects easier to develop.

    Appreciate any comments on my thoughts above.

    Cheers,

    Chris

  12. #12
    Join Date
    Apr 2007
    Posts
    5
    Further to my last post, you could have a ContactsTable, and if that contact is an Employee rather than having a roles list you could have a EmployeeContacts table which would have the additional employee fields such as passport, salary etc:

    Contacts
    ContactID_PK
    Name
    Address (or include seperate address table)
    Phone

    EmployeeContacts (1:1 join on Contacts.ContactID_PK to EmployeeContacts.ContactID_PK)
    ContactID_PK
    Passport
    Salary

    By definition, if a ContactsID exists in the EmployeeContacts table (1:1 join) they are be default considered an employee.

    Is this normal scenario for using 1:1 joins enabling the user to subclass and divide data? I did read 1:1 joins are not very common?

    The only trouble i can see is that the more data is spread across tables like the above scenario the more business logic that will need to be entered to ensure that the database remains in a valid state (i.e. EmployeeContacts must have an entry in the Contacts table which we can do by enforcing referential integrity).

    Thanks,

    Chris
    Last edited by g18c; 11-06-07 at 02:45.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by g18c
    ...However, for our employees we would want to store passport number, salary etc, additional fields we wouldnt want to store (or we wouldnt even know!) from suppliers or customers... what do we do in theses cases?...
    Unless there are scads of Employee-specific fields, just leave these null. Avoid 1:1 joins. They are a relic of a time when disk space was expensive and processing power was weak.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Scads.

    -PatP

Posting Permissions

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