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:
Cities where the compnay's operate:
Services that the companies provide:
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 !
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.
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.
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.
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.