Databases
StackOverflow2013
Postsdbo
POHow to design a searchable, versioned library of items with wildly varying properties and multiple editors?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to design a searchable, versioned library of items with wildly varying properties and multiple editors?
    primarykey
    data
    text
    <p><strong>What I need:</strong></p> <p>I'm designing the backend for a product library which has to satisfy the following requirements:</p> <ol> <li><p>Multiple editors will be editing different items at the same time -- there has to be some kind of item-level locking.</p></li> <li><p>Wildly varying item properties -- there are about a 100 subcategories, each of which can have 10+ item properties specific to itself.</p></li> <li><p>The whole item store has to be versioned -- multiple changes (insertions, edits and deletions) can be made before publishing the whole set of changes to the site; unpublishing must also be possible.</p></li> <li><p>I must be able to search all the properties and filter by some of them -- i.e. find a keyword anywhere in the library or find all products that satisfy a set of criteria -- within a data set of at least 10MB (i.e. 5000 items, 2KB each,) and possibly twice that.</p></li> </ol> <p>The solution should either be MySQL-specific or, better yet, vendor-agnostic.</p> <p><strong>What I've considered:</strong></p> <p>I'm considering using a single large XML object with all the items (to satisfy <strong>2</strong>) stored in a database (to satisfy <strong>3</strong>) but that makes <strong>1</strong> impossible and <strong>4</strong> difficult. I've used something like this before, but with smaller XML objects and no item-level locking.</p> <p>The other solution I'm considering is a classic database solution using a separate table for each subcategory, which makes <strong>1</strong> and <strong>2</strong> trivial, but <strong>3</strong> and <strong>4</strong> rather difficult. It's also a bit unwieldy considering the number of different subcategories and therefore number of different tables in the database, but I guess that can be automated.</p> <p>Another possibility is a hybrid between the two, with a single large database table of all items. Each row would contain an XML object with all the item's properties and additionally all the filtrable properties as table fields. This solves <strong>1</strong>, <strong>2</strong> and partially solves <strong>4</strong> but leaves out full-text searching and still makes <strong>3</strong> rather difficult to achieve.</p> <p><strong>If you've made it so far:</strong></p> <p>I'll probably have a few weeks to solve it, which should leave enough time for discussion. I'll be very grateful for any and all thoughts and insights the SO community can provide. Thanks in advance.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMichał T
    1. USMichał T
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to design a searchable, versioned library of items with wildly varying properties and multiple editors?
      1. USMichał T
      1. VTBountyStart
    2. VO
      singulars
      1. POHow to design a searchable, versioned library of items with wildly varying properties and multiple editors?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to design a searchable, versioned library of items with wildly varying properties and multiple editors?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI've seen option 3 implemented successfully. SQL Server full text search will index XML columns
      singulars
      1. POHow to design a searchable, versioned library of items with wildly varying properties and multiple editors?
      1. USMitch Wheat
    2. COHow would you solve versioning in this kind of system (i.e. the second and third solution)?
      singulars
      1. POHow to design a searchable, versioned library of items with wildly varying properties and multiple editors?
      1. USMichał T
    3. COHave you seen AutoAudit (codeplex)? Perhaps you could use this?
      singulars
      1. POHow to design a searchable, versioned library of items with wildly varying properties and multiple editors?
      1. USMitch Wheat
 

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