Hi, I am designing my database table customer, and I was so confuse on how can I deal with address can you help me please how can I deal with the address.
am I correct in my datatype use and length ?
CREATE TABLE `customer` (
`custid` INT(11) NOT NULL AUTO_INCREMENT,
`firsname` VARCHAR(50) NOT NULL,
`lastname` VARCHAR(50) NOT NULL,
`address` VARCHAR(100) NOT NULL,
PRIMARY KEY (`custid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
Thank yo in advance
what about city, state (if in the US, province in Canada, and elsewhere?) and zip/postal code?
yes, that is what I mean how can I break my address ?.
Thank you in advance.
do I need to create another table for the city,state,zip_code,postal_code ?
Only reason you’d want to consider having a second table is if a person can have multiple addresses (i.e. home and business). You just need separate columns on your table for them.
I think the customer has only 1 address. so it is okay to put the state,city,zip_code and postal code.
Thank you in advance.
This is the format of customer address from france.
RECIPIENT
[APARTMENT] [BUILDING]
HOUSE_NUMBER [STREET_TYPE] STREET_NAME [DEPENDENT_LOCALITY]
POSTAL_CODE LOCALITY
FRANCE
so the address of customer is compose with
apartment,
building,
house_no,
street_name,
postal_code,
locality
do I need to include the street_type,dependent_locality and locality ?
I am confuse.
Thank you in advance.
Most address table structures I have seen are like this:
Address1
Address2
City/Locality
State
PostalCode
Country
For your purposes,
Apartment and Building would go into Address 1 if they exist
House Number and Street Name would go into Address2 if there is an Apartment/Building, otherwise Address1
The rest would be pretty self explanatory.
I don’t know what would go into Street_Type or Dependent_Locality. If you can give me examples, I could probably give you a better idea of whether they should just be included into Address2 or if you need to put them somewhere else. I would venture to guess that keeping them in Address should be sufficient, but I’d need to see examples just to be sure.
since you tagged me, i’m going to offer my input as follows…
under what circumstances will you need to query your customers by a specific address? will you need to know which customers live in a house that has number 42 in any city? what about any customers that live in any house on Oak Street in any city? how about querying customers by postal code OR zip code?
also, who inputs the customer data? where does it come from?
answer those questions, and you’ll understand which columns you want
This is the example that I found.
JACQUES BENOIT
PARC CLUB DU MOULIN A VENT
22 ALLEE PICARD
69200 VENISSIEUX
FRANCE
Thank you in advance.
will you need to know which customers live in a house that has number 42 in any city?
no need to query number
what about any customers that live in any house on Oak Street in any city?
yes can query street and city
how about querying customers by postal code OR zip code?
yes postal code or zip code.
who inputs the customer data?
The customer will input
where does it come from?
During the registration.
Thank you in advance
Really? You’re going to have some one ask “May I have all the orders from Oak Street?” Or “Give me a list of all the orders, sorted by Street?” Unless you’re running a UPS, Fed Ex or whatever mass postal service, I sincerely doubt that.
The reason he was asking was if you need to query for or order by JUST that value, then it will need to be in it’s own field. Otherwise, it can be combined with another related field. If you look, what I gave you in #8 would work for 90% of what you need. Worse case would you would need a third address field, but in most cases, not.
Let’s look at the example you gave:
JACQUES BENOIT. Would you ever search for orders by someone named Jacque? Probably not. But would you search for orders by someone named Benoit? There’s a good possibility. So those two should be separate fields (FirstName, LastName).
PARC CLUB DU MOULIN A VENT - That looks like a building name, so why would you separate those out. One field should be sufficient (Address1)
22 ALLEE PICARD. Are you ever going to search for any orders where 22 is in the address? Probably not. Same for searching for any orders where the address is in an alley? Again, probably not. Would you search for any orders where the order had Picard in the address? Probably, but in this case the street is called “Allee Picard” (Picard Alley), so separating out those doesn’t make sense. And since you’re not likely to search for all orders on Allee Picard regardless of where, including the street number would be proper as well. So one field would be sufficient here as well. (Address2)
69200 VENISSIEUX. Would a client ever want to know how many orders ship to postal code 69200? Good chance of that. Same for any orders for the city of Venissieux (guess technically it’s a commune, but you get the idea). So in this case, two fields would be appropriate (PostalCode and Locality)
FRANCE. Yeah, pretty self explanatory here. (Country)
@DaveMaxwell Thank you for explaining 
I am thinking that I have search box in my form then I can search customer by city or street, then display all customers living in that city. I did not think that I can use SQL LIKE clause to Finds any values that have “example city” in any position of address field.
very nice exposition, dave, thanks
my question about who enters data (answer: the customer will input) was a hint that designing a web form for ordinary people has its own challenges, and will likely suggest the best fields (and hence table columns)
I need to know about joinquery between 4 tables, can you help me please?
You need to give some more details of what your issue is, and perhaps start your own topic if it’s not directly related to the current discussion.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.