3 Replies - 263 Views - Last Post: 17 July 2013 - 07:08 PM Rate Topic: ***** 1 Votes

#1 robrich22  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 19-September 12

Table Design

Posted 17 July 2013 - 06:17 PM

I have a table for customers, which stores basic info about customers.. Name, Address, Phone, etc. Now when designing the table. Do I want to store fields as a text string, or an numeric field and establish a relationship to a child table with the text. So the main table with be CUSTOMER_ID(primary key), FIRSTNAME, LASTNAME, CITY_ID(int/foreign), STATE_ID(int/foreign).

So then I'd have the tables.

CITY with columns CITY_ID(primary), CITY_NAME
STATES with columns STATE_ID(primary key), and STATE_NAME.

Or should I just store city and state as a text string in the customers table?

Thanks in advance.

- Rob

Is This A Good Question/Topic? 0
  • +

Replies To: Table Design

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8890
  • View blog
  • Posts: 33,338
  • Joined: 12-June 08

Re: Table Design

Posted 17 July 2013 - 06:28 PM

Depends - do you want to track previous or alternate addresses? If so then have your customer id be a foreign key to an 'address' table.. which has its own key, street, city, state, zip, and country.
Was This Post Helpful? 0
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3710
  • View blog
  • Posts: 5,958
  • Joined: 08-June 10

Re: Table Design

Posted 17 July 2013 - 06:33 PM

Storing addresses can be a tricky thing, if you want to keep it perfectly normalized. Most people tend not to do that though, opting for simpler solutions. - My explanation about the 3NF in my Relational Database Design - Normalization tutorial deals with this exact issue. You may want to check it out.
Was This Post Helpful? 1
  • +
  • -

#4 robrich22  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 19-September 12

Re: Table Design

Posted 17 July 2013 - 07:08 PM

View PostAtli, on 17 July 2013 - 06:33 PM, said:

Storing addresses can be a tricky thing, if you want to keep it perfectly normalized. Most people tend not to do that though, opting for simpler solutions. - My explanation about the 3NF in my Relational Database Design - Normalization tutorial deals with this exact issue. You may want to check it out.


Thanks, I will read your tutorial. I am also 30-40 pages into "Database Design for Mere Mortals".
Right now, I'm thinking of just keeping it simple, and storing the city/state names as a string/varchar.

But we'll see as I read further.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1