Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: Your Opinion on Normalization regarding Upsizing to SQL- Customer Relationship Mgt

    Hi,
    I'm building a CRM. I need to keep track of Contacts, the company that they work for, the company address and their mailing address. inititally I set up my tables like this:

    tblPerson(PersonID (pK),FName,LName,....)
    tblCompany(CompanyID (pK), CompanyName,...)
    tblCompnayPerson(PersonID (pK),CompanyID (pK),...)
    tblCompanyAddress(AddressID (pK),CompanyID (fK), Street,...)
    tblCompanyPerson_Adress( PersonID(pK),AddressID(pK))

    Now I'm trying to upsize the CRM from Access to SQL Server and I am facing some problems. First of all, althought the above design is nomalized, it is very hard to maintain specially since I need to import data all the time and I also might need to do some Syncronization.

    In addition to that, Its very hard to build forms in Access projects that are based on joins (since you can't update such views), were as in Access you could easily build a join query and base a form's record source on it.

    Any way, I am thinking to change the design as below:


    tblPerson(PersonID (pK),FName,LName,....)
    tblCompany(CompanyID (pK), CompanyName,...)
    tblCompnayPerson(PersonID (pK),CompanyID (pK),Street, Suite,City....)

    What do you guys think according to your experience?

    Thanks
    Last edited by Sia; 03-03-03 at 21:51.

  2. #2
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Having separate tables for a company/persons adress is totally useless, at least I think so. How often will two people or two businesses have the same adress in your database? My guess is not often enaugh to justify the hassle it is to have two more tables to look up.

    If I were to build such a database tblPerson and tblCompany would be plenty, only with CompanyID as an attribute to person. You shouldn't be normalizing just for the sake of having a normalized database, it also has to make sense. So "overnormalizing" like you have done in your first example seems a faste of time/resources to me. But then again I don't know what these import/synch-things you are talking about are so it might be a good idea to consider your options here. But in most cases I would go for the two-table-only solution...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks for your reply.
    The reason I overnormalized was that we might have 4 or 5 contacts from the same company. If all work at the same address then the address will be repeated 4 times.
    Now, if I put the companyID in tblPerson: What if a company has several addresses (in different states), then how would I know in which address my contact works? -> I would have to create a third table for Company address (AdresID...) and the fourth tblCompanyPersonAdres (AdresID,PersonID).

    Thats why I thought of the below design:
    tblPerson(PersonID (pK),FName,LName,....)
    tblCompany(CompanyID (pK), CompanyName,...)
    tblCompnayPerson(PersonID (pK),CompanyID (pK),Street, Suite,City....)

    at least this way I know in which address my contact works.




    Originally posted by Frettmaestro
    Having separate tables for a company/persons adress is totally useless, at least I think so. How often will two people or two businesses have the same adress in your database? My guess is not often enaugh to justify the hassle it is to have two more tables to look up.

    If I were to build such a database tblPerson and tblCompany would be plenty, only with CompanyID as an attribute to person. You shouldn't be normalizing just for the sake of having a normalized database, it also has to make sense. So "overnormalizing" like you have done in your first example seems a faste of time/resources to me. But then again I don't know what these import/synch-things you are talking about are so it might be a good idea to consider your options here. But in most cases I would go for the two-table-only solution...

  4. #4
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    This all has to do with the use and the size of your data. If you plan to have 5000+ contacts in there your initial design would probably be alot better than my contribution but if we are talking only a few then you donæt need it all. It all comes down to what you prefer and what works faster for you.
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

Posting Permissions

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