Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You need a slow changing dimension:</p> <p>i will do this only for e-mail and telephone so you understand (pay attention to the fact of i use two keys, 1 as unique in the table, and another that is unique to the user that it concerns. This is, the table key identifies the the record, and the user key identifies the user):</p> <p>table_id, user_id, email, telephone, created_at,inactive_at,is_current</p> <ul> <li>1, 1, mario@yahoo.it, 123456, 2012-01-02, , 2013-04-01, no</li> <li>2, 2, erik@telecom.de, 123457, 2012-01-03, 2013-02-28, no</li> <li>3, 3, vanessa@o2.de, 1234568, 2012-01-03, null, yes</li> <li>4, 2, erik@telecom.de, 123459, 2012-02-28, null, yes</li> <li>5, 1, super.mario@yahoo.it, 654321,2013-04-01, 2013-04-02, no</li> <li>6, 1, super.mario@yahoo.it, 123456,2013-04-02, null, yes</li> </ul> <hr> <p>most recent state of the database</p> <pre><code>select * from FooTable where inactive_at is null </code></pre> <p>or </p> <pre><code>select * from FooTable where is_current = 'yes' </code></pre> <hr> <p>All changes to mario (mario is user_id 1)</p> <pre><code>select * from FooTable where user_id = 1; </code></pre> <hr> <p>All changes between 1 jan 2013 and 1 of may 2013</p> <pre><code>select * from FooTable where created_at between '2013-01-01' and '2013-05-01'; </code></pre> <p>and you need to compare with the old versions (with the help of a stored procedure, java or php code... you chose)</p> <pre><code>select * from FooTable where incative_at between '2013-01-01' and '2013-05-01'; </code></pre> <p>if you want you can do a fancy sql statement</p> <pre><code>select f1.table_id, f1.user_id, case when f1.email = f2.email then 'NO_CHANGE' else concat(f1.email , ' -&gt; ', f2.email) end, case when f1.phone = f2.phone then 'NO_CHANGE' else concat(f1.phone , ' -&gt; ', f2.phone) end from FooTable f1 inner join FooTable f2 on(f1.user_id = f2.user_id) where f2.created_at in (select max(f3.created_at) from Footable f3 where f3.user_id = f1.user_id and f3.created_at &lt; f1.created_at and f1.user_id=f3.user_id) and f1.created_at between '2013-01-01' and '2013-05-01' ; </code></pre> <p>As you can see a juicy query, to compare the user_with the previews user row...</p> <hr> <p>the state of the database on 2013-03-01</p> <pre><code>select * from FooTable where table_id in (select max(table_id) from FooTable where inactive_at &lt;= '2013-03-01' group by user_id union select id from FooTable where inactive_at is null group by user_id having count(table_id) =1 ); </code></pre> <p>I think this is the easiest way of implement what you want... you could implement a multi-million tables relational model, but then it would be a pain in the arse to query it</p> <hr> <p>Your database is not big enough, I work everyday with one even bigger. Now tell me is the money you save in a new server worthy the time you spend on a super-complex relational model?</p> <p>BTW if the data changes too fast, this approach cannot be used...</p> <hr> <p>BONUS: optimization:</p> <ul> <li><p>create indexes on created_at, inactive_at, user_id and the pair </p></li> <li><p>perform partition (both horizontal and vertical)</p></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POHow to optimise change history data for MySQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSQL.injection
    1. USSQL.injection
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload