Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Rokie with a design problem...

    Hey Guys,
    First of all thank you very much for at least and up to this point show some interest in helping me out ! lol...
    I'm trying to design a DataBase but I'm not the best when it gets to designs, so please if you're gonna help out, pretend that you're talking to a 3 years old kid ! lol
    Here's the scenario/diagram:

    Company Name:
    A
    B
    C

    Cities where the compnay's operate:
    aa
    bb
    cc

    Services that the companies provide:
    1
    2
    3

    The posible results

    Company A is in aa bb cc and provides in aa 123 in bb 1 and in cc 13


    They way that this is gonna work is the following a comany is gonna register with all their info. ONce that's done is gonna have to list all the cities where this company operates, on submit, the next page will display the list of cities and check marks to specify which services are provided on each of the cities.
    Therefore, there will be many companies, operating in different cities some of them on the same city and each company provides certain services according with the city. Therefore there will be more then one company operating in the same city as other companies and offering the same services as the other companies.

    I hope this was clear enough, this many to many relationships are killing me...
    Once again thanks for all your help and support !

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Companies (CompanyID, CompanyName)
    Cities (CityID, CityName)
    Services (ServiceID, ServiceName)
    Locations (LocationID, CompanyID, CityID)
    LocationServicies (LocationID, ServiceID)
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Companies (CompanyID, CompanyName)
    Cities (CityID, CityName)
    Services (ServiceID, ServiceName)
    Locations (CompanyID, CityID)
    LocationServices (CompanyID, CityID, ServiceID)

    the Locations table is probably not required, since a company that offers no service in a particular city can rightfully be deemed not to be operating in that city
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I have a question gentlemen.

    If the below requirement:
    Quote Originally Posted by Calvin256
    each company provides certain services according with the city.
    was instead:
    Quote Originally Posted by Calvin256
    each company provides its full repertoire of services to each city it serves
    Would you still have proposed the same designs. If not, why not?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, because i can get the desired result with a CROSS JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you mean the following design:

    Companies (CompanyID, CompanyName)
    Cities (CityID, CityName)
    Services (ServiceID, ServiceName)
    Locations (C
    ompanyID, CityID)
    CompanyServices (CompanyID, ServiceID)


    And then cross join Locations with Company Services?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937
    Companies (CompanyID, CompanyName)
    Cities (CityID, CityName)
    Services (ServiceID, ServiceName)
    Locations (CompanyID, CityID)
    LocationServices (CompanyID, CityID, ServiceID)

    the Locations table is probably not required, since a company that offers no service in a particular city can rightfully be deemed not to be operating in that city
    What? No harping about the surrogate keys?
    But the Locations table probably WILL be required, to store attributes about the location. Plus it helps in maintaining relational integrity in the LocationServices table by preventing services from being specified in cities where the company does not operate.

    Quote Originally Posted by pootle flump
    I have a question gentlemen.

    If the below requirement:was instead:Would you still have proposed the same designs. If not, why not?
    No, because then the service is an attribute of the company, not an attribute of the company location.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by pootle flump
    Do you mean the following design:

    Companies (CompanyID, CompanyName)
    Cities (CityID, CityName)
    Services (ServiceID, ServiceName)
    Locations (C
    ompanyID, CityID)
    CompanyServices (CompanyID, ServiceID)


    And then cross join Locations with Company Services?
    No, you don't want to cross join to the Services table unless every company offers the same services.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    What? No harping about the surrogate keys?
    it was there, just below your level of consciousness


    Quote Originally Posted by blindman
    But the Locations table probably WILL be required, to store attributes about the location.
    as i understood the spec, locations = cities
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    No, you don't want to cross join to the Services table unless every company offers the same services.
    which is why you would cross join to the CompanyServices table as pootsie suggested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - you are right, with that design you would join on companyid.

    The reason I asked is that I believe both designs were in BCNF even with that requirement change, but not 4NF. Both your changes, as I understand them, put the design into 5NF. I've thought for a while that 4&5NF problems actually come about from incorrect entity selection in the early stages of db design, and these errors are not rectified by just following the normal forms to 3NF.
    I've had, however, a sneaky suspicion that the wiley old experienced modellers would spot the error without explicitly referring to multi-valued dependencies. I just saw an opportunity to test this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    error? what error? i spotted no error, except that stupid surrogate key that blindman invented

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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    p.s. yeah, it's not a CROSS JOIN, is it -- it's a join on only one of the two PK columns

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

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937
    error? what error? i spotted no error, except that stupid surrogate key that blindman invented
    Had I only thought to patent it, I would be a rich man today.
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I mean that the solutions to the original question were in 5NF. When I changed the criteria they were only BCNF. This is the 'error' I meant. Yet both of you decomposed the relation in response to the change, thus putting the model back into 5NF.
    As I recall both of you claim to normalise to 3NF only. I thought your responses were interesting.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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