Geek asks and answers

What is the best way to optimize traffic collection schemes

We have a sports training camp in which various teams in the city regularly participate. We have a session per day covering 2 hours (9-11 am), and time intervals can vary for different teams. We would like to capture those who attended the training camp daily.

We came to the next model to capture attendance. (id, user_id, date, present). Assuming the user visits the camp daily (say, 30 days a month), you will see that there are a lot of records in the database.

Assuming that we are only interested in knowing the number of days during which the user visited the camp, is there a better way to note the presence or absence of a specific user (perhaps only once a month and mark all the individual days as something like (P , P, P, A, ..., A, P). P = Present, A = None

+3
source share
4 answers
AttMst
  id | date

AttDet
  attdetid | id | userid

Thus, you need to save the day in AttMst, and current users on that day will be saved in AttDet.

+1
source

You must ask yourself why you do this.

There are several possibilities, but it is likely that your database schema will not be fully normalized.

So, first of all: what do you want to achieve and what are the reasons for this?

Some features:

: , , - , , count

+2

"" , , .

, . , , , ( ). , . , . . , , , , .

. . , , , . : , TEAM_SESSIONS. TEAM_SESSIONS, .

Oracle.

SQL> desc teams
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(20 CHAR)

SQL> desc sessions
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 SSN_DAY                                            DATE
 SSN_START                                          NUMBER(4,2)
 SSN_END                                            NUMBER(4,2)

SQL> desc team_sessions
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEAM_ID                                   NOT NULL NUMBER
 SESSION_ID                                NOT NULL NUMBER

SQL>

PIVOT, Oracle 11g, ( ). , , , Bec United - ( )!

SQL> select * from (
  2      select t.name as team_name
  3             , trim(to_char(s.ssn_start))||'-'||trim(to_char(s.ssn_end)) as ssn
  4             , case when ts.team_id is not null then 1 else 0 end as present
  5      from   sessions s
  6             cross join teams t
  7             left outer join team_sessions ts
  8                  on (ts.team_id = t.id
  9                      and ts.session_id = s.id )
 10      where s.ssn_day = trunc(sysdate)
 11      )
 12  pivot
 13      ( sum (present)
 14        for ssn in ( '9-11', '11-13', '13-15', '15-17', '17-19')
 15      )
 16  order by team_name
 17  /

TEAM_NAME                '9-11'    '11-13'    '13-15'    '15-17'    '17-19'
-------------------- ---------- ---------- ---------- ---------- ----------
Balham Blazers                0          1          0          0          0
Bec United                    1          0          0          0          1
Dinamo Tooting                0          0          0          0          0
Melchester Rovers             0          0          0          1          0

SQL>

, . , , , , , .. , . , , .

, - - , , , . , BCNF - .

+2

IMHO, , , , , .

, , ? , , , , . , " ", .

, :

id | user_id | date | present


user_id | month | attendance

user_id, .

0
source

More articles:


All Articles