Thinking of the primary key of database design

According to the second paradigm, the primary key is required. The primary key is still unique, and the primary key is also referenced as a foreign key to establish a relationship between the table and the table. Discuss the primary key from these aspects (the database is Oracle):

1. Naming the primary key

Recently seen because of the reason that the schema uses hibernate, causing all primary key names to be IDs, I feel very bad, such as the Department Table (department), the user table (username), the role table (roles), the tables if the associations are associated with IDs, Very difficult to identify this is called the ID is the table, if the change to department_id,user_id,role_id is not very comfortable, a look will know is the ID of the table. Unfortunately, the architectural constraints, even if the developers constantly complaining, there is no way.

2. What fields do you choose as the primary key

Choose the primary key to find a natural key (the key that is related to the business), or to build a key that has nothing to do with the business model? To make an analogy:

Department Table (Department) There is a department code is unique, coding rules such as,

Baidu Company 01

Baidu Company/Research and Development Department 0101

Baidu Company/research/development/Search engine Development Group 010101

Device has the device Code field, which is a unique identifier that is generated based on some of the device's properties.

My advice is to build a field that has no business meaning, what is the reason?

Departments will be adjusted, a department from the big department under the adjustment to other large departments is often the case, there are many business related to the Department of Information, if the base table is adjusted, it needs to be the business of the data are refreshed.

The code for the device table may also change because the device type is adjusted every year, as long as the adjustment code changes, as is the case with the department.

Speaking of this has brothers unconvinced, our company's equipment table code does not adjust. What I want to say is that you can't predict the future, as long as it's business, it can change.

3. Whether the primary key is a selection sequence or a UUID

If your system is a small system, the amount of data is not big, then there is nothing to pay attention to.

A. If there are 21 municipal councils in Guangdong, a system is issued in each bureau, and every day, the data of the municipal bureau should be extracted to the provincial company integration. If you use a sequence, it is difficult to add the data to a provincial company if you do not make any processing by adding the sequence to the front of the board. If the UUID does not need to consider this problem, they claim to be the world's only.

B. With sequence, which performance is good for UUID? I have really tested this, the UUID does not have a good sequence of performance, but almost the point, can be ignored. The UUID is a 32-bit varchar2, taking up more space than the sequence, so performance is almost no surprise. What is not to say that any sequence of scenes is better than a UUID? You can't say that, there is a problem with the sequence, which I found in my long-term performance tuning, with a sequence that could cause SQL statements to be fast and slow. If the normal use of the sequence, the primary key is continuous, there is no problem, difficult is sometimes impossible, such as your department ID from 1 to 100, due to data migration reasons, you want to distinguish between the previous department ID and migration, you put the sequence from 10000, which will result in uneven data. If you know the histogram, binding snooping, then I don't have to explain.

4. There is also a special case where there is now a sectoral table (department), a user table, and an associated table, which may cause duplication of problems.

Create Table dept_user_relation

(

relation_id Number () Primary Key ,

department_id Number (),

user_id Number ()

);

relation_iddepartment_id user_id

----------- ------------- ----------

1 100 100

2 100 100

3 100 100

You will find that the primary key relation_id not work Ah! Yes, you need to add a unique constraint on department_id and user_id, and when you add a constraint, you'll find out what's the use of this relation_id? Yes, it may not work.

A. If RELATION_ID is not referenced by another table as a foreign key, you can use department_id and user_id to unite as the primary key. But I don't think it's a problem to keep, of course, if you're a Virgo, you only have to delete relation_id.

B. If relation_id is referenced by another table as a foreign key, it is recommended that you keep it or not.

What I want to say in this section is that the essence of the primary key is a constraint, which indicates uniqueness.

Thinking of the primary key of database design