Design table with address

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`)

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.

@DaveMaxwell, @r937

This is the format of customer address from france.


so the address of customer is compose with


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:


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.


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 :slight_smile:

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)

1 Like

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.