Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Ok, I might get flamed for not answering your question directly but I'm going to say it anyway because I think it's something you should consider. I don't have experience with NOSQL databases so I can't recommend one but as far as relational databases go there might be a better design for your situation.</p> <p>First of all - drop the 1 table per customer. Instead, I would architect a many to many schema in which there would be the following tables:</p> <ul> <li>Customers</li> <li>MeasurementTypes</li> <li>Measurements</li> </ul> <p>The Customers table will contain customer information, and a unique CustomerID field:</p> <pre><code> CustomerID | CustomerName | ..and other fields --------------------------------------------------------------------- </code></pre> <p>The MeasurementTypes table would describe each type of measurement that you support, and assign a unique name (the MeasurementType field) to refer to it:</p> <pre><code> MeasurementType | Description | ..and other pertinent fields --------------------------------------------------------------------- </code></pre> <p>The Measurements table is where all the data is aggregated. You would have one record for each data point collected, stamped with the customer id, the measurement type, a time stamp, and a unique "batch" identifier (to be able to group data points from each measurement together) - and of course the measurement value. If you need different types of values for your measurements you may need to get a little creative with the design but most likely the measurement values can all be represented by a single data type.</p> <pre><code> Customer | MeasurementBatch | MeasurementType | Timestamp | Value | -------------------------------------------------------------------------------- 1 | {GUID} | 'WIND_SPEED' | ... | ... -------------------------------------------------------------------------------- | | | | | </code></pre> <p>This way, you can have a very flexible design that would allow you to add as many data points for each customer independently from other customers. And you get the benefits of relational databases..</p> <p>If your SQL engine supports this feature you could even partition the Measurements table by the customer column.</p> <p>Hope this helps..</p> <p><strong>EDIT</strong></p> <p>I must mention that I'm not in any way affiliated with Microsoft nor am I trying to give them free advertising - it just so happens I'm most familiar with their SQL server.</p> <p>Based on Alan's comment - regarding whether a SQL database can support a data volume of a few thousand million records per year with the possibility of growing up to a billion records per year - there is a nice summary of limitations/specs for MS SQL server available here:</p> <p><a href="http://msdn.microsoft.com/en-us/library/ms143432.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms143432.aspx</a></p> <p>It seems that the only limitation to how many records you can have per table is the available size on disk (and probably RAM if you're going to want to run certain reports on that data). </p>
    singulars
    1. This table or related slice is empty.
    1. PONeed Advice: Is this a good use case for a 'NoSQL' Database? If so, which one?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMike Dinescu
    1. USMike Dinescu
    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
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWe have looked into something like this. The issue we foresee would is the size of the measurements table. At our current 15 minute data collection rate we would hit 5.25 million records per project per year. When we move to a one minute interval we are talking 78.8 million records per year per project. Then with 100 projects we would hit 7 billion records per year. Is that something MySql can handle?
      singulars
      1. PO
      1. USAlan Peabody
    2. COI'm not sure about MySQL's limitations in terms of # of records per table but with careful database design (i.e. partitioning) a product like MS SQL Server or Oracle should have no problem handling a few hundred million records in a table.. Especially since each record would be rather small. You could use an integer identifier for the MeasurementType and use a unix timestamp for and a record would come out to between 32 and 44 bytes depending of the data type you use for Value.
      singulars
      1. PO
      1. USMike Dinescu
 

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