Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'd recommend the latter design you mention, create one dependent table with five columns:</p> <pre><code>CREATE TABLE Items ( user_id INTEGER NOT NULL, item_id INTEGER NOT NULL DEFAULT 1, numeric_property1 INTEGER, numeric_property2 INTEGER, referential_property INTEGER, PRIMARY KEY (user_id, item_id), FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE, FOREIGN KEY (item_id) REFERENCES num_items(item_id), FOREIGN KEY (referential_property) REFERENCES some_other_table(some_column) ); </code></pre> <p>I show a table <code>num_items</code> above, which contains the numbers 1 through 10 if you want to restrict users to 10 items at most:</p> <pre><code>CREATE TABLE num_items (item_id INTEGER NOT NULL ); INSERT INTO num_items (item_id) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); </code></pre> <p>Advantages of this design is that it's easy to <code>COUNT()</code> how many items a given user has, it's easy to compute things like <code>MIN()</code> and <code>MAX()</code> for a given property, you can enforce a foreign key for the referential property, etc.</p> <p>Some databases have a feature to declare the second part of a compound primary key (<code>item_id</code> in this case) as auto-incrementing, so if you specify the value for <code>entity_id</code> but omit <code>item_id</code> it automatically gets the next unused value (but does not fill gaps if you delete one). You don't state which brand of database you're using so I'll leave it to you to figure out this feature.</p> <p><strong>edit:</strong> As Tony Andrews says in his answer, the number of rows is not a problem. You don't state which brand of database you're intending to use, but unless you choose an especially feeble product like MS Access, you can rely on the database to process millions of rows easily. If you choose indexes well, and write queries that use those indexes, efficiency shouldn't be a problem.</p>
    singulars
    1. This table or related slice is empty.
    1. PODatabase Design regarding Dynamic entries - One row, or multiple rows?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBill Karwin
    1. USBill Karwin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODatabase Design regarding Dynamic entries - One row, or multiple rows?
      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
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWouldn't it be better to use a CHECK constraint for item_id instead of a foreign key?
      singulars
      1. PO
      1. USDave
    2. COCheck my edit. I agree, but I'm still concerned with the efficiency of what I'll be doing as the number of rows is going to be huge.
      singulars
      1. PO
      1. USMunkiPhD
    3. COYes, you could use a CHECK constraint but when you need to expand to 12 items, modifying a CHECK constraint is a metadata change (ALTER TABLE), and that may be expensive. Inserting values (11), (12) is an easy data change.
      singulars
      1. PO
      1. USBill Karwin
 

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