Is This Mysql Database Normalization Form and Structure Design Correct? -

i trying figure out kind of normalization , structure use database making. going list of properties(building number street addresses, street names, cities, states, zipcodes, unit numbers).

from there, going make table various info. going have intermediate table join information , make record. far can tell, every column multi valued except unit number. so, see need complete normalization:

table building_number --------------------- building_number_id int primary key auto index not null buildind_namber tinyint  table city -------------------- city_id building_number_id int primary key auto index not null city_name varchar(30)  table state -------------------- state_id building_number_id int primary key auto index not null state_name varchar(30)  table zip --------------------- zip_id building_number_id int primary key auto index not null zip_name varchar(30)  table building_name --------------------- building_name_id int primary key auto index not null building_name varchar(50)  table owner --------------------- owner_id int primary key auto index not null owner_name varchar(30)   table info ---------------------- info_id int primary key auto index not null rent tinyint condition varchar(10) comment varchar(1000)  intermediate table -------------------------- building_number_id int  street_id int  city_id int  state_id int  building_name_id  owner_id  info_id  (all these keys foreign keys referencing respected tables/primary keys) 

i creating html search text box take dynamic input , pull queries based on whatever give...complete exact address, street name, or building number street name city, etc. haven't developed search mysql algorithm yet. i'm @ beginning stage of creating database.

i using innodb engine , b-tree indexing. index every column except comment since doing these dynamic input searches(like google).

i doing myself hobbyist. because of this, prefer hand scratch rather using framework or plug ins.

for doing, database design , normalization correct?

when you're creating tables, should thinking first in terms of entities, , in general terms, entity tangible thing.

examples of tangible things are: buildings, owners, contacts, cities, countries, time zones.

on other hand, there things not entities, instead descriptors of entities.

examples of descriptors are: height, weight, door number, , price.

descriptors attributes of entities. if not possible enumerate possible descriptors in advance, these should not in table.

cases want look-up table descriptor you're constrained in types of values can accept. example, "shoe size" might seem open-ended, maybe manufacture sizes, free-form input field not practical. on other hand, "height" better stored value pre-defined set of units instead of having look-up table of possible heights.

in case, need "address" entity number of fields describe it. things "building number" should free-form input field. "building a", "82 1/2", "107b", "3.7", "4/9" , "44-290" valid building numbers. should accept string.

likewise, street names hardly thing can qualify. "green way street" same "green way st." or "greenway st."? matter? not, it's descriptor. have no way of verifying these, , linking them impossible, there's way massaging required work on large scale.

also keep in mind places need two, three, four, or 5 lines of address information identify location. united kingdom 1 of worst offenders here, formal address include sorts of information.

what should design table "addresses" fields: address1, address2, address3, address4, address5, city, region, country, postal_code. can cover they'll throw @ you. @ kind of data google maps returns examples.

you seem hinting @ kind of one-to-many structure in question, address have multiple building names or numbers. without kind of sequence indicator, you'll have no way of knowing of these associated records first. complicates things significantly.

when worrying normalization, start simplest thing works, , fix obvious mistakes. unless have massive amounts of data deal with, can adjust schema if haven't over-done normalization.