Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>As a general rule <em>never</em> store in a table what you can calculate on exit from it. For instance, you want age, you should store date of birth. If you want the sum of two columns, you should store those two columns, nothing else.</p> <p>Maintaining the data-integrity, -quality and -consistency in your database should be your paramount concern. If there is the slightest chance that a third column, which is the sum of the first two, could be out-of-sync then it is not worth doing. </p> <p>As you cannot maintain the column without embedding the calculation into all code that inserts data into the table (open to being forgotten in the future and updating may break it) or firing a trigger every time you insert something (lots of additional work) you should not do this.</p> <p>Your situation is a perfect use-case for views. You need to consistently calculate a column in the same way. If you let everyone calculate this as they wish then the same problems as with inserting the calculated column occur, you need to guarantee that this is always calculated the same way. The way to do this is to have a view on your table that pre-calculates the column in a standard way, that will be identical for every user.</p> <blockquote> <p>Calculating a sum hundreds of time would be much costlier then reading it from somewhere... right?</p> </blockquote> <p>Not necessarily, this depends entirely on your own situation. If you have slower disks then reading the data may easily be more expensive then calculating it. Especially since it's an extremely simple calculation.</p> <p>In all likelihood it will make no difference at all but if it is a major performance concern you should test both situations and decide whether the potential loss of data-quality and the additional overhead in maintaining the calculation in a table is worth the odd nano-second on extraction from the database.</p>
    singulars
    1. This table or related slice is empty.
    1. POMySQL : Storing business logic in table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USBen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMySQL : Storing business logic in table
      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. VTUpMod
    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. VTAcceptedByOriginator
    1. COThat makes sense, but I am wondering why there was no performance improvement after I created a view and queried the view.
      singulars
      1. PO
      1. USuser1263746
    2. CO@user1263746 performance over what? A view is simply a stored query. Perhaps you're thinking of a materialized view (which stores the result of the query in a snapshot table)?
      singulars
      1. PO
      1. UStbone
    3. CO@tbone Oh, you are right. I think that's what I want. Thanks for the pointer.
      singulars
      1. PO
      1. USuser1263746
 

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