Posted by J.O. Aho on 06/10/06 17:51

Frankly wrote:
> "J.O. Aho" <user@example.net> wrote in message
>> Frankly wrote:

>>>> This requires some javascript coding too, if you want the zip code box
>>>> to be automatically filled when you entered the area. Otherwise if you
>>>> allow people to leave the box empty, you can let the receiving php page
>>>> to fetch the zip code (keep in mind that an area may have more than one
>>>> zip code, so it's really better to do it the other way around, fill the
>>>> zip code and let the php script fetch the area name from the database
>>>> and then save it into the row).
>>> I am not sure how i would create this table.
>> ZipTable
>> ZipCode AreaName
>
>
> I thought a table like this would create dupes.
> if i understand this correctly I could put multiple zip codes in a single
> column :).

No, you wouldn't get dupes, as

WA98108 Seattle

ain't the same as

WA98109 Seattle

even if both are located in Seattle, with some work you can even add another
column which tells which part of town the zip code belongs and that way get
even better check on locations that you normally would have.



>>> I guess allowing null would let some records have more zip codes than
>>> others.
>> No nulls, always a zip code and the area name, otherwise you get troubles.
>
>
> i remember reading something that showed me how... I could help protect
> myself by making it so
> that I cant put an apartment, unless there is a building, and cant put a
> building unless there is a management.
> so the same thing for areas and zipcodes.

When you create a column you can use

AreaName VARCHAR(40) NOT NULL

this prevents null values in a column.


> I have already done - Managements, Buildings and apartments tables today.
> i guess this script will be in the form I create later on for inputing new
> records.
> what I had planned for today was to build all the tables, give my best shot
> at making relations,
> and input some records. then start learning how to make those records show
> on my web site.
> after that start working on those kinds of forms.
> Does that sound like a good plan?

Yes, but I do my relations on paper before I create tables, even if you have
ALTER http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
it's better to get the tables right from the beginning than altering them
afterwards.



>> Here you can do two different things, just save the zip code, you can then
>> joint the buildings table with the zip table, the draw back to do this is
>> that the more zip codes and buildings you have, the longer time it will
>> take to make the join.
>> The faster way is to store both area and zip code in the building table,
>> but still allow only the user (your wife?) to type the zip code in the
>> form for saving the building to the database, in your php script you get
>> the area name from the zip table, and then save this name to the building
>> table, this way you always have the same spelling on the areas. I know
>> here that sometimes people uses ' ' (space) or '-' (hyphen) on names that
>> is "two" parted as 'Los Angeles' which could be written badly as
>> 'Los-Angeles'.
>
> ok, zipe code and area fields will be in the buildlings table. I guess I
> will understand
> this more once i start working on those forms :).

As I said much depends on how you want to do things, in a database you avoid
to store the same data in all to many places, exception is when you get
performance problems, then you start to duplicate data to speed things up.


> While i do feel a bit lost with this new admin tool. I do believe in the
> long run I will
> be able to do more while asking less questions. to tell you the truth if
> "I" get this thing done
> I dont understand why anyone would limit them selves to MS. I am still a
> bit nervous about it. I see
> even Strawberry is asking questions on how to do things. back to work.

I'm sure you will manage this well, the worst things that can happen is that
you manage to crash the server ;) Really, it's not that bad, but a badly
chosen DELETE can result in data loss, but creating a table that you think is
wrong will not cause in data loss, just create a new table and export the data
to the new table before you DROP the old table, this way you will not loose
your data.


//Aho

[Back to original message]