Turret Legend Guild Management system------Pgsql backstage

Background:

The system will give the situation of the guild staff, such as a few months a few days XXXX joined the guild, a few months a few days XXXX left the union.

The system gives the total number of active people for the last seven days.

The average active for the last seven days is calculated, and players with less than seven days are added to the actual days.

Data storage options:

1. Start using the Excel do, write some macros, add a button to trigger this macro. With a little more data, the ills are revealed.

When entering active data, you need to manually look for the name in the first few lines. Multi-day data processing needs to be handled manually, which can easily cause data confusion or data loss when the data volume is large.

2. You plan to use an Access database for storage, but you find that Query Analyzer for access needs to be downloaded separately and supports only one sentence of SQL execution at a time.

3. Replace the MySQL under window to store the data. After all the installation, after creating the database table, found that the contents of the table is not very good support for Chinese. Coding problems, on-line to find a simple solution, seemingly need a new version, and still need to operate a lot. There is a need to refer to http://www.jb51.net/article/18560.htm

4. I compare data pgsql, or use this bar, there is no coding problem, completely free open source. After a toss-up, finally in Win7 under the fix.

Database design:

In order to achieve good results, but also to turn over the book to review the concept of the paradigm, feel that the relational database can do 3NF is nice.

In order not to generate a large amount of redundant data, planning to talk about member management is separated from the contribution value data. Member the primary key in the table is intended to use the in-game Chinese name (you can also give each member an internal ID, the effect should not be many).

The fields in the member table are name,indate,outdate. The name and Indate fields are not Null. If outdate is null, the member is currently in the Union.

The data table will have about 50 new data per day, and when it takes a long time to produce a large table, it will inevitably affect access efficiency. It's hard to change again. This Schedule data table is named by date, which means that a new table is generated every day. There is only one field in the table, which is an active value of the integer type.

Source:

--Table:gonghui.member--DROP TABLE gonghui.member;CREATE TABLEGonghui.member (Namecharacter varying( -) not NULL, Indatecharacter varying( -) not NULL, Outdatecharacter varying( -),  CONSTRAINTMember_pkeyPRIMARY KEY(name)) with(OIDS=FALSE);ALTER TABLEGonghui.member OWNER toRoot

--Table:gonghui.data20141209--DROP TABLE gonghui.data20141209;CREATE TABLEgonghui.data20141209 (Namecharacter varying( -) not NULL, the datainteger,  CONSTRAINTData20141209_pkeyPRIMARY KEY(name),CONSTRAINTData20141209_name_fkeyFOREIGN KEY(name)REFERENCESgonghui.member (name) MATCH Simple on UPDATENO ACTION on DELETENO ACTION) with(OIDS=FALSE);ALTER TABLEgonghui.data20141209 OWNER toRoot

--Function:gonghui.lesssevendays (integer)--DROP FUNCTION gonghui.lesssevendays (integer); CREATE OR REPLACE FUNCTION gonghui.lesssevendays (Days integer) RETURNS integer as$body$beginif(Days >7) Thenreturn 7; Endif; if(Days <1) Thenreturn 1; Endif; returnDays ; END; $BODY $ LANGUAGE plpgsql VOLATILE Cost -; ALTER FUNCTION gonghui.lesssevendays (integer) OWNER to root;

lesssevendays (Days integer)

--Function:gonghui.fn_calc_result (character varying, character varying)--DROP FUNCTION Gonghui.fn_calc_result (character varying, character varying);CREATE OR REPLACE FUNCTIONGonghui.fn_calc_result (psv_datecharacter varying, Psv_compare_datecharacter varying)  RETURNS integer  as$BODY $DeclarePsv_sqlcharacter varying( +) := "'; Psv_tbl_newcharacter varying( +) := "'; Psv_tbl_oldcharacter varying( +) := "';BEGINDrop TableDataresult; Psv_tbl_new:= 'Data' ||psv_date; Psv_tbl_old:= 'Data' ||psv_compare_date; Psv_sql:= 'CREATE TABLE Dataresult as select M.name as Name, Lesssevendays (To_date (" " ||Psv_date|| " "," "|| 'YYYYMMDD" "|| ')-Date (m.indate)), D.data/lesssevendays (To_date (" " ||Psv_date|| " "," " || 'YYYYMMDD" "|| ')-Date (m.indate)) as Avg_data, D.data as NewData, Y.data as OldData from member M,' ||Psv_tbl_new|| 'D,'||Psv_tbl_old||'y where m.name = d.name and m.outdate =" '"and m.name = Y.name'; EXECUTE(Psv_sql); return 0;END; $BODY $ LANGUAGE plpgsql VOLATILE Cost -;ALTER FUNCTIONGonghui.fn_calc_result (character varying,character varying) OWNER toRoot

Fn_calc_result (character varying, character varying)

Turret Legend Guild Management system------Pgsql backstage