Thanks for taking a look at that LunarDraco. At first, the database was going to be configured with no surrogate keys where there was a logical candidate key. At least for parent tables. Then I found out that cakephp does not support compound keys, so I had to abandon that plan. Also, I found out that it is generally better to do as you said with using separate program-use-only surrogate keys in this type of application. Apparently there are entire Vi/Emacs style debates of surrogates vs. logical data representation. At any rate, I have altered that in almost all cases, and with your recommendation may alter the rest.
Thank you so much for your information about the zip codes; I actually had no idea that was the case. I will make the changes you recommend. I finally posted the full thing for people to see. Here is the full diagram so far, without the updated zip code set up. Keep in mind these are just representing the entities not the tables hence the ClassName instead of table_names. pdf: http://www.filedropper.com/mcidclassesrev6 dia: http://www.filedropper.com/mcidclassesrev6_1 On Mar 19, 12:21 pm, LunarDraco <[email protected]> wrote: > The problem I see with your model is in your ZipLocation you have the > code set to a PK. > > The Problem: > It is often thought that a zipcode belongs to only one city. And that > a city can have many zipcodes. There are no such rules in the postal > system. > > One zip code could and often overlaps many city boundaries. Especially > in rural areas. > So your ZipLocation Table as it is defined could not have records like > the following without violating the PK: > 84087, Woods Cross, UT > 84087, West Bountiful, UT > > I see this design quite a bit and this is where you will run into > trouble. But probably not until you get quite a ways down the path of > development and start adding a bunch of data. > > You should follow the naming scheme for table names and special field > names like (id, [fkmodel]_id, name, description, created, modified) > this will help tremendously during the baking of your model as cake > will pick up all the appropriate hasOne, hasMany, belongsTo, etc. This > will also help in your building your forms for creating your dropdowns > and autofill text as if your models have a name field it is used in > the dropdown. I don't like to see user data stored in the PK. If you > need it to maintain uniqueness (which you don't want to in this case) > there are other ways to guarantee the user data is unique. PK and FK > should be for the data relations, and program use only. > I would review section 2.4 of the > manualhttp://book.cakephp.org/view/22/CakePHP-Conventions > which talks about all of the file naming, model and database, > controller and view conventions. > > Creating the tables as below allows you to have multiple cities in the > same zipcode and still maintain proper relations between the tables. > Your Model relations still remain as Location hasOne ZipLocation, > State hasMany ZipLocation, ZipLocations belongTo Locations, and > ZipLocations belongTo States. And your Form can display the proper > autofill selections when the user enters 84087 or any other zips that > might have multiple cities. > > -------------- > locations > -------------- > id (PK) > name > street1 > street 2 > zip_location_id (FK) > ... > > -------------- > zip_locations > -------------- > id (PK) > zipcode (indexed) > city > state_id (FK) > > -------------- > states > -------------- > id (PK) > code > name Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en To unsubscribe from this group, send email to cake-php+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
