Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Some thoughts, if not necessarily an answer:</p> <p>Clearly a change log is a must-have for you, so the original structure with a single row per user is not a solution for you. So we're talking about the choice between:</p> <ol> <li>A single row per version of each user's entire information-set; or</li> <li>A single row per version of the each user's item of information</li> </ol> <p>Solution 1 corresponds to your </p> <pre><code>userLog ( id INT, date TIMESTAMP, email VARCHAR, phone VARCHAR, address VARCHAR ) </code></pre> <p>Solution 2 corresponds to the Wordpress one:</p> <pre><code>umeta_id INT, user_id INT, meta_key VARCHAR, meta_value VARCHAR </code></pre> <p><strong>Your question 1:</strong> I can't see any advantage to Solution2 except that, if you subsequently decide you want to capture users' (for example) Website URL or (for example) favourite colour as well, you can do that by adding a meta_key. But you could equally easily do this under Solution1, by simply doing an</p> <pre><code>ALTER TABLE userlog ADD COLUMN WebSiteURL(etc) </code></pre> <p>That's not hard to do. Unless the DBAs in your shop are unusually Dobermann-like ( ;) ). Because you're holding a change-log, all existing users (at the time of the change) will now have a blank WebsiteURL column; but that's exactly what you want: you don't know their WebsiteURL, because the system didn't capture it before. Sure, the new column will have to be NULLABLE - but that may be unavoidable anyway, even with the "initial" data, unless the method you're using to capture user info insists on email, phone and address as required columns.</p> <p>To me, the disadvantages of the meta_key solution outweigh the advantages. The disadvantages are:</p> <ul> <li><p>You have to develop a piece of pivot code to pivot user info for one user onto one<br> row. You must call this code in every place you want to get user info on one row. In contrast, Solution1 only requires</p> <p>SELECT userID,[all user info] FROM userLog INNER JOIN (SELECT userID,MAX(datechanged) AS LatestDAteChanged FROM userlog GROUP BY userID) a ON userlog.userid=a.userID AND userlog.DateChanged=a.LatestDAteChanged</p> <p>which is far more efficient than a pivot. With an index on UserID,DateChanged, this'll run like the wind.</p></li> <li><p>Unless you really want to hold meta_key values multiple times in the userinfo table (Email, Email, Email, Email, Email), you'd need an extra Meta_Key_Lookup table.</p></li> </ul> <p><strong>Second question:</strong> For ultimate space-efficiency, yes, the meta_key Solution2 is the best. Especially if you don't use VARCHAR metakeys, but metakey ID values, and have a separate meta_key lookup table (e.g. 1=Email, 2=Phone etc). But I don't think this is a conclusive argument for the meta_key Solution2, given the virtually-zero price of storage, and the difficulties involved in this solution.</p> <p>(A note/thought: IMHO your idea of holding NULL values in your solution1, where the value has not changed, is a wrong road. The coding to try to get the most recent email, then phone, then address (separately) for each user, will be a nightmare: almost as hard to code/test - and for the server to run - as the pivot required by the other solution. And the reduction in storage marginal. Just hold the entire row every time one thing changes. Unless you're just giving examples, and the real user info-set is 50 columns wide...)</p> <p>IMHO the storage issue is not decisive. So let's turn to SELECT/INSERT efficiency:</p> <p>On this issue, I think Solution1 still wins. On Inserts, SOlution1 wins: only one row is inserted, even if the user changes every field in their info. On SELECTS, SOlution 1 wins again: you only need a view of the most recent info per user (code above), which is the kind of thing SQL is optimised for. In contrast, Solution2 would require a pivot: something SQL is not good at.</p>
    singulars
    1. This table or related slice is empty.
    1. POWhich layout for user-data plus a change-log is the most efficient and less storage consuming?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USsebt
    1. USsebt
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhich layout for user-data plus a change-log is the most efficient and less storage consuming?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COActually I will insert, or rather update, on a single row, per user. The change log that holds NULL values will be populated by triggers. I think that this is the best approach I can think of now. Similar to what you said in your last paragraph.
      singulars
      1. PO
      1. USMario
    2. COAn advantage of a generic solution (meta_key) is that it would allow you to store the change log of multiple tables (with an additional table key or a column key unique for all tables).
      singulars
      1. PO
      1. USOlivier Jacot-Descombes
    3. COHey, @sebt, in case you are interested... I did a test with two tables holding a row per user. One holding the entire data set of each user and the second one having only the changed data and NULL for the rest of the cells. Inserted around three million of random changes for different users in both tables and compared their size in hard disk. Guess what, both were exactly the same... really curious, maybe Postgres already does some kind of optimization for this kind of cases. Well, there you go. :P
      singulars
      1. PO
      1. USMario
 

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