如何为包含地址的User表设计数据库？

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

我想创建一个包含地址字段的User表,地址是用户定义的类,它还包含字段

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

Address不是字符串,而是包含各种字段的用户类

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?

如何为这种场景设计数据库,如果我创建单独的用户和地址表,我将如何引用用户表中的地址字段?

3 个解决方案

#1

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.

SQL方面,如果每个用户都有一个地址 - 将两者都保存在同一个表中。这种方法将为您节省不必要的,可能是错误的JOIN操作。查询时,必须将结果中的每一行拆分为两个类。

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 address.id value.

这意味着地址表具有主键,通常是唯一的数字ID。 user表有一个address_id字段,其值是有效的address.id值。

For example,

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

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

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

意味着George Carlin住在Sausalito,Richard Pryor住在旧金山。

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.

您可以施加其他限制。例如,在user表中使每个地址id唯一(以便两个用户不能具有相同的地址),或者强制每个用户拥有地址的非null约束。

#2

You have some possibilities, depending on situation:

你有一些可能性,取决于具体情况:

1- Adam's scenarios #1, if user has just an address, keep data in the same table. I.e.

1- Adam的场景#1,如果用户只有一个地址,则将数据保存在同一个表中。即

            create table user
            ( ID number not null primary key,
              ...other user fields...
              street varchar(100),
              ...other address fields
              );

2- Adam Scenario #2, address shared by several users, in 2 separate tables

2- Adam Scenario#2,由多个用户共享的地址,在2个单独的表中

            create table user
            ( id number not null primary key,
              ...other user fields...
              **address_id number foreign key references address(id)**
             );


             create table address
            ( **id number not null primary key**,
              ...other address fields...
             );

3- One user may have several addresses. In this case you've a foreign key in table address, referencing user

3-一个用户可能有多个地址。在这种情况下,您在表地址中有一个外键,引用用户

            create table user
            ( id number not null primary key,
              ...other user fields...

             );


             create table address
            ( **id number not null primary key**,
              ...other address fields...
              **user_id not null foreign key references user(id)**
             );

4- many user, having many addressess , with users that can by sharing some addresses, in this case you have a connection table with references both user and address table.

4-许多用户,有很多地址,用户可以通过共享一些地址,在这种情况下,你有一个连接表,引用用户和地址表。

            create table user
            ( id number not null primary key,
              ...other user fields...
             );


             create table address
            ( **id number not null primary key**,
              ...other address fields...
             );

             create table user_address(
                **user_id number foreign key references user(id)**,
                **address_id number foreign key references address(id)**,
                constraint user_address_pk primary key (user_id,address_id)
             );

5- In case 1, you may possibly want to mantain a one-to one relation, but i think is not the case.

5-在案例1中,您可能想要保持一对一的关系,但我认为情况并非如此。

I normally use scenario 3

我通常使用方案3

#3

You can declare a TYPE in SQL for storing address

您可以在SQL中声明TYPE以存储地址

eg.

DECLARE 
TYPE address IS RECORD 
(
    address_id number(5), 
    address_hno varchar(20),
    address_street varchar(100),
    address_city varchar(50),
    address_zip varchar(20),
    address_state varchar(100),
    address_country varchar(100)
)

You can use this type while creating a user table and refer this TYPE instead of pre-defined datatypes.

您可以在创建用户表时使用此类型,并引用此TYPE而不是预定义的数据类型。

Hope this helps.

希望这可以帮助。