How to create a database for a chain store

In the chain software, there are no more than two types of database structures. One is that all data is stored in the same database, including the headquarters, and the other is the independent headquarters database. Each branch has one database, I often use independent databases. These two methods have their own advantages. when data is stored in the same database, it is very convenient for data exchange between branches, such as statistics.

In the chain software, there are no more than two types of database structures. One is that all data is stored in the same database, including the headquarters, and the other is the independent headquarters database. Each branch has one database, I often use independent databases. These two methods have their own advantages. when data is stored in the same database, it is very convenient for data exchange between branches, such as statistics.

In the chain software, there are no more than two types of database structures. One is that all data is stored in the same database, including the headquarters, and the other is the independent headquarters database. Each branch has one database, I often use independent databases.

These two methods have their own advantages. data is stored in the same database, and data exchange between branches is very convenient. Statistics and other such methods are also directly operated on the database, not so much trouble. The bad thing is that there is a lot of data, which is not easy to maintain. You have to consider paging and other issues. The second method is just the opposite of the first method, but I think the data will be very clear and easy to maintain if it is separated. Of course, this is only for scenarios where there is not much interaction between our software databases, some databases may interact with each other frequently, that is, they use the first method. If there is no maintenance problem, they will also choose the first method. Each method is good or bad and depends on each user's usage.

An independent database has to deal with the problem of how to create a database when creating a branch. Because the software is constantly updated, that is, the database is also constantly updated, it is also necessary to create a Branch database flexibly.

(Mssql) the earliest method used: a database backup file (Bak) will be stored in the Headquarters. Each update overwrites the Bak file, and the idea of creating a database is as follows: 1. back up the model database to a model. bak, 2. restore the bak file to the model. you can directly use the create database statement to create a Branch database. 4. use model. bak restores the database to the model database. [Note: When a database is created, it is created using the model database template. This is exactly what we use here]

This method can be used to create a branch, but it is stupid. Each update requires adding a database bak file to the Update file, and the database keeps increasing. Although it is a blank database, only the table structure and so on, but it also requires 8 M +, which brings a lot of trouble during the update, so we will come up with another method to improve it based on the original.

(Mssql) improvement methods: 1. when installing the software, a template database will be created on mssql server. This template database can also be seen as a database of a branch. This database will be updated when the software is updated, after the update, the template database will be backed up, covering the Bak files in the Headquarters. The creation of the database at the branch is still unchanged, just like the above. The major improvement here is the database template problem. In this way, you do not need to carry the database file with each update.

The original software has been using this method until now.

Recently I have a set of new software and encountered this problem in database design. I always felt that the improved method was not very convenient, so I thought of the following improvement method.

1. Keep the template database in use. The template database is updated with the branch updates. The Bak file cannot be used and is voided. 2. when creating a branch, first create an empty database, and then read all the structures of the template database to the new database through the program. Simply put, all the structures of the template database are copied through the program.

In this way, you no longer need to use heavy files such as Bak, and the database can be an independent server. The old method is that the database and software must be the same server, because the Bak files stored in the headquarters should be relied on, now the database and software can be separated. The template database is the latest, and the structure of the new database copy template database created by the Branch has been solved.

Let's talk about Mysql, because our software is compatible with multiple databases, and Mysql is also one of them. The idea of mysql and mssql is the same in the early stage, both by updating database files, use the database backup file to create a new database, and Mysql is also changed to this optimal method: 1. after the software is installed, a template database is automatically created. when creating a new branch, the database is backed up into an SQL file, and the database name in the SQL file is modified in the program. run the SQL file. The difference between mysql and mssql is that mssql copies the table structure and must read the template data one by one and then execute it on the new database. mysql backup is used to export all the database structures, we only need to modify the name of a new database for execution.

The following describes how to copy a database in C:

Public void InitialData (string ModelDataBaseName, string DataBaseName) {# region processes table and table data string strSql_GetUserTable = string. format ("select * from {0 }. dbo. getUserTable order by id, columnsort ", ModelDataBaseName); DataTable dt_GetUserTable = dbHelper. executeQuery (CommandType. text, strSql_GetUserTable, null ). tables [0]; if (dt_GetUserTable.Rows.Count> 0) {// id, tablename, columnsort, columnname, tablek Ey, columndatatype, columndatalen1 // values, columndatafloat, allowisnull, defaultdata string tableName = ""; StringBuilder strCreateTableSql = new StringBuilder (); StringBuilder strKeySql = new StringBuilder (); stringBuilder strInsertData = new StringBuilder (); foreach (DataRow dr in dt_GetUserTable.Rows) {if (dr ["tablename"]. toString ()! = TableName) {if (! String. isNullOrEmpty (strCreateTableSql. toString () {if (strKeySql. length> 0) {strKeySql. remove (strKeySql. length-1, 1); strCreateTableSql. appendFormat ("primary key clustered ({0}) ON [PRIMARY]", strKeySql. toString (); strKeySql. remove (strKeySql. length-1, 1);} else {if (strCreateTableSql. toString (). endsWith (",") strCreateTableSql. remove (strCreateTableSql. length-1, 1); strCreateTable SQL. append (")");} dbHelper. executeNonQuery (DataBaseName, strCreateTableSql. toString (), null); dbHelper. executeNonQuery (CommandType. text, string. format ("insert into {0 }. dbo. {1} select * from {2 }. dbo. {1} ", DataBaseName, tableName, ModelDataBaseName), null);} tableName = dr [" tablename "]. toString (); strCreateTableSql. clear (); strCreateTableSql = new StringBuilder (); strKeySql. clear (); strKeySql = New StringBuilder (); strCreateTableSql. appendFormat ("create table {0} (", tableName);} strCreateTableSql. append (dbHelper. getDataType (dr ["columnname"]. toString (), dr ["columndatatype"]. toString (), dbHelper. getInt (dr ["columndatalen2"]. toString (), dbHelper. getInt (dr ["columndatafloat"]. toString (), dr ["allowisnull"]. toString (), dr ["tablekey"]. toString (), ref strKeySql);} if (! String. isNullOrEmpty (strCreateTableSql. toString () {if (strKeySql. length> 0) {strKeySql. remove (strKeySql. length-1, 1); strCreateTableSql. appendFormat ("primary key clustered ({0}) ON [PRIMARY]", strKeySql. toString (); strKeySql. remove (strKeySql. length-1, 1);} else {if (strCreateTableSql. toString (). endsWith (",") strCreateTableSql. remove (strCreateTableSql. length-1, 1); strCreateTableSql. append (")");} dbHelper. executeNonQuery (DataBaseName, strCreateTableSql. toString (), null); dbHelper. executeNonQuery (CommandType. text, string. format ("insert into {0 }. dbo. {1} select * from {2 }. dbo. {1} ", DataBaseName, tableName, ModelDataBaseName), null) ;}# endregion # region stored procedure, view, function, trigger StringBuilder strSql = new StringBuilder (); strSql. appendFormat ("select name, type, definition from {0 }. dbo. getUserPV ", ModelDataBaseName); DataTable dt_GetUserPV = dbHelper. executeQuery (CommandType. text, strSql. toString (), null ). tables [0]; if (dt_GetUserPV.Rows.Count> 0) {StringBuilder strTest = new StringBuilder (); foreach (DataRow dr in dt_GetUserPV.Rows) {dbHelper. executeNonQuery (DataBaseName, string. format ("{0}", dr ["definition"]. toString (), null) ;}# endregion}