Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If I understand you correctly, this is a database modelling question. To start with your specific questions:</p> <blockquote> <p>Should each new run of a schedule create a new table?</p> </blockquote> <p>No. A table represents a <em>type</em> of thing (as would a class in an OO implementation). If you need to distinguish groups of rows in that table, then that is an attribute of those rows, and probably a separate entity in its own right. So you would have a table with a row for each "new run of a schedule", and then refer to that in your other table with a Foreign Key.</p> <blockquote> <p>Should I do sorting with arrays before I ever enter data into the MySQL table?</p> </blockquote> <p>No. The order rows are entered into a database table should never be used to impart meaning. If they have some particular order, the information to create that order should be in a column of the table. You sort data when you <em>retrieve</em> it from the database, using an <code>Order By</code> clause. (<strong>Always</strong> include an <code>Order By</code> clause whenever you are <code>Select</code>ing data.)</p> <p>Your second set of bullet points seems to jump from how to <em>model</em> the data to how to <em>query</em> it. So first, read up on:</p> <ul> <li>Database Normalization: in brief, every entity should have its own table, and the relationships between them defined by Foreign Keys</li> <li>Entity-Relationship Diagrams: don't worry about learning all the official symbols, but drawing out your data on a Visio diagram or a piece of paper can really help understand how things relate</li> </ul> <p>Once you have a decent idea of what your entities are, and a normalised structure for them, the rest should just be about how to write queries relating to them:</p> <ul> <li>"Has this group done this activity in the last X number of scheduled periods?" might involve a <code>JOIN</code> between a <code>groups</code> table, a <code>groups_scheduled_activities</code> pivot table (i.e. one that exists only to relate two or more tables in a many-to-many relationship), and a <code>scheduled_periods</code> table.</li> <li>"Do not always start from the top of the activities list ... maybe even be able to prioritize the importance" is all about how you select from the <code>activities</code> table when you're displaying it in the UI: perhaps an <code>Order By</code> clause involving your priority factors and a random factor. (Random ordering in some DBs can be tricky, but look around and you'll find plenty of solutions.)</li> <li>If I understand right, "factor in another schedule to exclude them from the scheduled list for the days they will be gone" would just be a particular kind of "activity" that lasted a long time and maybe was presented differently in the UI. From a data modelling point of view, this just needs an attribute on activities (i.e. a column on the <code>activities</code> table) to distinguish these from "normal" activities. Presumably, activities would have a "duration" attribute anyway.</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POCreating Scheduler App - process and concept questions
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USIMSoP
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCreating Scheduler App - process and concept questions
      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. VTAcceptedByOriginator
    1. COOK, I edited to include planned changes - is this what you were thinking? Any other suggestions?
      singulars
      1. PO
      1. USred6
    2. CO@red6 The suggestions in your edit include a lot of columns where they should have rows - you're trying to squeeze multiple things into one table. You also mention having a table to store dates, which will generally be unnecessary, because you can just use the date itself. I've put together [a quick example of a normalised schema](https://gist.github.com/IMSoP/6653849) based on what you've mentioned so far.
      singulars
      1. PO
      1. USIMSoP
    3. COI've implemented most of your suggestions and am working on finishing it today. Your last bullet point was correct, if I think of it as a 'hidden' activity, or one that is presented elsewhere, it can just be stored the same way.
      singulars
      1. PO
      1. USred6
 

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