Database design: A table structure design method for enumeration types, personal humble opinion

Enumeration, a data type (as opposed to the C # language, of course, java,php), and the use of enumerations in projects have some of the following advantages.

First: The definition is intuitive, easy to use,

Second: Easy to maintain and extend (actually intuitive)

For the display of data of enumerated types, it is generally defined within the program such an enumeration, and then through the program to get the enumeration corresponding to the

Key name, or DescriptionAttribute tag, or custom Attribute, which is processed when data is bound and

Displays the description information or key name of the corresponding enumeration value, which increases the running cost and the calculation cost of the program, personal humble opinion!! ）

Look at the question:

There is an order form, a single data represents an order basis information, the operation of the order, the order status needs to be limited.

For example, the order status is as follows:

1: Pending, this situation allows the warehouse personnel to make a single, survival package, do packaging operations,

2: Waiting for the goods, this situation allows the warehouse personnel to print the order, do pick-up operations,

3: In the distribution, this situation allows the warehouse personnel to sort order items,

4: To be packaged, this condition allows the warehouse personnel to package the sorted items into a package.

5: To weigh, packaging completed, allowing the warehouse personnel to the parcel before the shipment of the parcel weighing operation.

6: To be shipped, has been packaged, and weighing completed, waiting for delivery to the customer,

7: Shipped, shipped, monitoring of parcel logistics information

8: Signed, the user has signed the package (the last step of the order, marking the completion of the order.) ）

I am now going to display the key or description of the corresponding enumeration by querying the statement directly, without invoking any methods.

I do it the way I do!

use tempdbgo--  Create order Form Create table sys_order (    ID BIGINT  IDENTITY (10000,1)  PRIMARY KEY,    [Status] INT NOT NULL,     createdate datetime not null default (GETDATE ())) go--  creating an enumeration type  table basic_enum_type (    id int identity ()  PRIMARY KEY,     name nvarchar ( not null,    [langid] int)  NOT NULL,    --  language Id    [desc] nchar ()   NULL) go--  Enumeration of the dictionary table Create table basic_enum_dict (    id int identity ( )  PRIMARY KEY,    [Key] BIGINT NOT NULL,     TypeId INT NOT NULL,        --  enum types, referencing Basic_ Enum_type the ID field of the table      name nvarchar ( not null,    [desc] nchar)   NULL) Go alter table basic_enum_dict add constraint c_unique_key unique ([Key ]) go--  initialize the data. Insert into  basic_enum_type (Name,[langid],[desc])  values (' Order Status enumeration ', 1, ') Insert into   basic_enum_dict ([Key],typeid,name,[desc]) select 1,1, ' pending ', ' this case allows warehouse personnel to make a single, survive package, do packaging operations, '   union allselect 2,1, ' Waiting for the goods ', ' this case allows the warehouse personnel to print the packing list, to do pick-up operations, '  union allselect 3,1, ' in stock ', ' In this case, the warehouse personnel are allowed to sort order items, '  union allselect 4,1, ' to be packaged ', ' in this case allow warehouse personnel to make a single, survive packages, do packaging operations, '  UNION  allselect 5,1, ' to weigh ', ' allow warehouse personnel to carry out parcel weighing operations before shipment, '  union allselect 6,1, ' to be shipped ', ' has been packaged and weighed, awaiting shipment to customer ,, '  union allselect 7,1, ' shipped ', ' already shipped, monitoring package logistics information, '  union allselect 8,1, ' signed ', ' The user has signed the package (the last step of the order, marking the completion of the order.) ), '    insert into     sys_order ([status],createdate) SELect 1,getdate ()  union allselect 1,getdate ()  union allselect 1,getdate ()  union allselect 1,getdate ()  union allselect 1,getdate ()  UNION  Allselect 1,getdate ()  GO --  order query now, including the status of the query. You can do this and write the following SQL statement.  select    _so.id as orderid,    isnull (_bed. Name, ' unknown ')  as statustext from sys_order _so with (NOLOCK)  left join  basic_enum_dict _bed  on _bed. Typeid=1 and _so. Status=_bed. [Key]

I personally feel there are several benefits in this way.

First, enumeration is more intuitive, easy to maintain,

Second, the query shows the query for the linked list. For programs to calculate the display, the overhead is smaller. Especially the CPU.

The script file has been uploaded and interested friends can download and view the attachment.

Personal humble opinion, welcome all friends, master criticism points out the shortcomings!!

This article is from the "idiot" blog, make sure to keep this source http://hotboy.blog.51cto.com/8826994/1640321

Database design: A table structure design method for enumeration types, personal humble opinion