Databases
StackOverflow2013
Postsdbo
POWhat's a good approach for allowing users to define their own custom types?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat's a good approach for allowing users to define their own custom types?
    primarykey
    data
    text
    <p>Say you’re designing an application that, by requirement, allows a user the flexibility of creating custom types (for managing his data, whatever it may be). One way of handling this is to define a schema which allows us to use metadata for defining these types. This often means the resulting db schema will have some way of storing key/value pairs (the properties belonging to an instance of a type), where the value portion is usually stored as a string (regardless of the underlying datatype of the column). This alone presents a number of issues. I’ve read that <a href="http://geekswithblogs.net/darrengosbell/articles/KVPsInDatabaseDesign.aspx" rel="nofollow noreferrer">some</a> <a href="https://stackoverflow.com/questions/126271/key-value-pairs-in-relational-database">frown upon</a> using a db to track key/value pairs. </p> <p>The main issue I have with it is how it affects querying. For example, let's say the user wants to create a type called <code>Event</code> having these columns: <code>event_name</code>, <code>description</code>, <code>start_at</code> and <code>end_at</code> (datetimes). Using key/value pairs where all values are strings makes queries more sensitive to how the parameter values are formatted; therefore, querying for a set of events falling between two dates is not as simple as it would be if we were using actual datetime columns.</p> <p>This moves me to consider alternative designs which would accommodate custom types. The first one that came to mind and which I liked the most was to use the database itself to define these custom types. That is, rather than create a separate set of meta-tables into which all types must be defined, simply permit the user a limited privilege to create/modify his own tables within the database (all of which would be prefixed with his username: e.g. <code>usertable-johndoe-album</code>). The most prominent issue I see with this approach is the sheer number of tables that might ultimately exist. I wonder if most open-source databases (MySQL, Postgres, etc.) have either a hard or a practical limit on how many tables they can manage without being impeded. That is, I know most production-ready databases are tuned to handle millions of records, but I don’t know if they’re equipped to handle hundreds of thousands of tables. Does anyone know?</p> <p>Given the requirement for allowing users to create their own types, do you prefer key/value pairs or utilizing the database itself? Or if you have another pattern/idea, please describe it.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USMario
    plurals
    1. PL
      singulars
      1. LTLinked
    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. POWhat's a good approach for allowing users to define their own custom types?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhat's a good approach for allowing users to define their own custom types?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhat's a good approach for allowing users to define their own custom types?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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