Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>There are a few (and thank goodness <strong>only</strong> a few) unbendable rules about relational database models. One of those is, that if you don't know <strong>what</strong> to store, you have a hard time storing it. Chances are, you'll have an even harder time retrieving it.</p> <p>That said, the reality of business rules is often less clear cut than the ivory tower of database design. Most importantly, you might want or even need a way to introduce a new property without changing the schema.</p> <p>Here are two feasable ways to go at this:</p> <ol> <li>Use a datastore, that specializes in loose or inexistant schemas (NoSQL and friends). Explaining this in detail is a subject of a CS Thesis, not a stackoverflow answer.</li> <li>My recommendation: Use a separate properties table - here is how this goes:</li> </ol> <p>Assuming for the sake of argument, your products <strong>allways</strong> have (unique string) <code>name</code>, (integer) <code>id</code>, <code>brightness</code>, <code>contrast</code>, <code>chromaticity</code> plus <strong>sometimes</strong> (integer) <code>foo</code> and (string) <code>bar</code>, consider these tables</p> <pre><code>CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, brightness INT, contrast INT, chromaticity INT, UNIQUE INDEX(name) ); CREATE TABLE properties ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, proptype ENUM('null','int','string') NOT NULL default 'null', UNIQUE INDEX(name) ); INSERT INTO properties VALUES (0,'foo','int'), (0,'bar','string'); CREATE TABLE product_properties ( id INT PRIMARY KEY AUTO_INCREMENT, products_id INT NOT NULL, properties_id INT NOT NULL, intvalue INT NOT NULL, stringvalue VARCHAR(250) NOT NULL, UNIQUE INDEX(products_id,properties_id) ); </code></pre> <p>now your "standard" properties would be in the <code>products</code> table as usual, while the "optional" properties would be stored in a row of <code>product_properties</code>, that references the product id and property id, with the value being in <code>intvalue</code> or <code>stringvalue</code>.</p> <p>Selecting products including their <code>foo</code> if any would look like</p> <pre><code>SELECT products.*, product_properties.intvalue AS foo FROM products LEFT JOIN product_properties ON products.id=product_properties.product_id AND product_properties.property_id=1 </code></pre> <p>or even</p> <pre><code>SELECT products.*, product_properties.intvalue AS foo FROM products LEFT JOIN product_properties ON products.id=product_properties.product_id LEFT JOIN properties ON product_properties.property_id=properties.id WHERE properties.name='foo' OR properties.name IS NULL </code></pre> <p>Please understand, that this incurs a performance penalty - in fact you trade performance against flexibility: Adding another property is nothing more than <code>INSERT</code>ing a row in <code>properties</code>, the schema stays the same.</p>
    singulars
    1. This table or related slice is empty.
    1. PODatabase Structure for Inconsistent Data
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USEugen Rieck
    1. USEugen Rieck
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODatabase Structure for Inconsistent Data
      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
    1. COHmm that is an interesting solution. Ive never seen anything like that before. Thanks a lot. I will give it a whirl
      singulars
      1. PO
      1. USMatt Hintzke
 

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