Amol Amol - 10 months ago 61
SQL Question

How to design database for User table which contains address?

I want to create a User table which contains address field as well and the address is a user defined class which further contains fields

Now the situation is like this :

  1. User class contains the address object

  2. Address is not a string but user class which contains various fields

contents of class User:

class User
private String name;
private int id;
private Address address;
private String martial_status;
private String employed_status;
private Date createdate;

Contents of Address class:

class Address
private String hno;
private String street;
private String city;
private int zip;
private String state;
private String country;

How do i design database for such a scenario , if i create separate User and Address tables , how will i refer to address field in user table

Thanks in Advance

Answer Source

Single table - each row has user and address data

SQL-wise, If each user has an address - keep both in the same table. This approach will save you unnecessary, and potentially erroneous, JOIN operations. When you query, you will have to split each row in the result into two classes.

Different user and address tables

If only a few user have addresses, or if the same address might be shared with many users, build two table and JOIN them with a reference from the user table to a primary key in the address table. Each table will be associated with its own class.

This means that the address table has a primary key, usually a unique numeric id. The user table has an address_id field, whose value is a valid value.

For example,

| id        | ...    |  city          |
| 1         | ...    |  Sausalito     |
| 1         | ...    |  San Francisco |

| name           |  ...   |  address_id |
| George Carlin  |  ...   |  1          |
| Richard Pryor  |  ...   |  2          |

Means that George Carlin lives in Sausalito, and Richard Pryor lives in San Francisco.

There are other restrictions you can impose. For example making each address id unique in the user table (so that two users can't have the same address), or a non-null constraint which enforces each user to have an address.