General base table Logic Design (i)--configuration, enumeration, and category

first, the configuration table

A system often has a lot of parameters that need to be customizable, such as the site name, contact address and other basic parameters and various system operating parameters, and so on, this time requires a separate parameter configuration table to save these parameters. The first thing you might think of is a parameter plus a field, but the number of parameters is not necessarily fixed, so the design needs to modify the database, modify the program, extensibility is not enough. Therefore, this column design can be converted to row design, the parameter increment and decrement by the data row to achieve.

Field name	Data type	Property	Allow empty	Default value	Note
cfg_id	Int (4)	PK AI			Configuration number
Cfg_guid	CHAR (36)			(NEWID ())	Configure GUID
Cfg_parent_guid	varchar (36)			(' 0 ')	The parent configuration GUID, which allows you to configure grouping by this field and unlimited hierarchy
Cfg_code	nvarchar (100)			('')	Configure encoding
Cfg_name	nvarchar (100)			('')	Configuration name
Cfg_desc	nvarchar (200)			('')	Configuration description
Cfg_value_type	varchar (50)			('')	Configure value types, such as bool (integer), HTML (HTML), or not, and different value types can be displayed and edited by different forms
Cfg_value	nvarchar (1000)			('')	Configuration values
Cfg_state	Int (4)			(1)	Configuration status (-1: deleted; 0: Disabled; 1: Enabled)

Table 1:base_config (Configuration table)

second, the enumeration table

Many times, the system needs to encode-name corresponding dictionary enumeration table to record what the various encodings mean, such as the order status of the code and meaning, the business and commodity types of coding and meaning, and so on, we need a special dictionary enumeration table to save this information. This information can also be read into the cache directly to get the meaning of the corresponding field encoding, thereby reducing the inter-table connection.

Field name	Data type	Property	Allow empty	Default value	Note
enum_id	Int (4)	PK AI			Enumeration number
Enum_guid	CHAR (36)			(NEWID ())	Enumeration GUID
Enum_parent_guid	varchar (36)			(' 0 ')	The parent enumeration number GUID, which can be implemented by this field to enumerate groups and unlimited levels
Enum_code	varchar (50)			('')	Enumeration encoding
Enum_value	Int (4)			(0)	Enumeration values
Enum_name	nvarchar (100)			('')	Enumeration name
Enum_desc	nvarchar (100)			('')	Enumeration description
Enum_state	Int (4)			(1)	Enumeration state (-1: deleted; 0: Disabled; 1: Enabled)

Table 2:base_enum (enumeration table)

iii. List of categories

In addition, businesses have categories (such as food and beverage, entertainment, etc.), products have categories (snapping, selection, etc.), these categories of information can also be abstracted into the category table.

Field name	Data type	Property	Allow empty	Default value	Note
cat_id	BigInt (8)	PK AI			Category number
Cat_guid	CHAR (36)			(NEWID ())	Category GUID
Cat_parent_guid	varchar (36)			(0)	Parent category GUID, which can be used to implement category groupings and unlimited levels
Cat_name	nvarchar (100)			(' 0 ')	Category name
Cat_key	nvarchar (100)			('')	Category keywords
Cat_desc	nvarchar (200)			('')	Category description
Cat_state	Int (4)			(1)	Category status (-1: deleted; 0: Disabled; 1: Enabled)
Cat_order	Int (4)			(0)	Category ordinal
Cat_detail	nvarchar (-1)			('')	Category Introduction

table 3:base_category (category table)

Iv. Summary

In reality, there are different kinds of data, after which the information can be abstracted into various types of business models, and finally materialized into the database design, which is embodied by the data table and other objects. The ability to analyze problem-solving issues, including data collection, induction, analysis and synthesis, should take into account the current set of facts and possible future changes in the design of specific data tables, with scalable, customizable designs to respond to frequent and changing realities.