Databases
StackOverflow2013
Postsdbo
POWhich layout for user-data plus a change-log is the most efficient and less storage consuming?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhich layout for user-data plus a change-log is the most efficient and less storage consuming?
    primarykey
    data
    text
    <p>My users can update their information, which is saved in a defined number of columns in a table, such as: <code>user ( id INT, email VARCHAR, phone VARCHAR, address VARCHAR )</code>, for example.</p> <p>I have seen other implementations, like the one for Wordpress, that stores this information for its users in a table called <code>usermeta</code> with a layout <code>( umeta_id INT, user_id INT, meta_key VARCHAR, meta_value VARCHAR )</code>.</p> <p>In the change log that I want to implement, I am evaluating between using a solution like that or making (what I think that will be better), a layout like: <code>userLog ( id INT, date TIMESTAMP, email VARCHAR, phone VARCHAR, address VARCHAR )</code>.<br> So, I can have a history of all the information any user had at a given date. Rows would only record the changes, having NULL on unaltered columns.</p> <p><strong>For the first question</strong>: Is there any advantage to this kind of layout other than being able to create new information type by just inserting an appropriate <code>meta_key</code>?<br> I sometimes think that this layout can be not really appropriate if performance is a matter in my environment, since I would be using a <code>VARCHAR</code> for every single kind of data that I want to store.</p> <p><strong>For the second question</strong>: Can storage and select/insert efficiency really make a difference between the two solutions I am considering?<br> Which solution should be less (or more) space-consuming and/or less (or more) select/insert efficient than the other and why?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USUnhandledExcepSean
    1. USMario
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhich layout for user-data plus a change-log is the most efficient and less storage consuming?
      1. USRS999
      1. VTFavorite
    2. VO
      singulars
      1. POWhich layout for user-data plus a change-log is the most efficient and less storage consuming?
      1. USMario
      1. VTBountyStart
    3. VO
      singulars
      1. POWhich layout for user-data plus a change-log is the most efficient and less storage consuming?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat database system are you using?
      singulars
      1. POWhich layout for user-data plus a change-log is the most efficient and less storage consuming?
      1. USsufleR
    2. CO@sufleR MySQL and PostgreSQL.
      singulars
      1. POWhich layout for user-data plus a change-log is the most efficient and less storage consuming?
      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