Databases
StackOverflow2013
Postsdbo
PODatabase within a database (table design)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase within a database (table design)
    primarykey
    data
    text
    <blockquote> <p><strong>Possible Duplicate:</strong><br> <a href="https://stackoverflow.com/questions/12127814/database-design-to-create-tables-on-the-fly">Database design to create tables on the fly</a> </p> </blockquote> <p>I have a need to create a database within a database. This question is related to <a href="https://stackoverflow.com/questions/12127814/database-design-to-create-tables-on-the-fly">this</a>. I will attempt to explain what i am trying to do in detail and provide examples in code. basically, I want to ability to create dynamic tables in a database. For example, I will have a webpage that allows users to create their own table with columns and data. Here is the database design I came up with:</p> <pre><code>aColumn aDataType aRow aTable zBit zDateTime zMoney zNumber zText </code></pre> <p>Tables that begin with a <code>z</code> are data that specific data go in such as ints, datetime values, etc. aColumn are columns that belong to a specific table. aRow identifies a particular row within the aTable. Here is the database design:</p> <pre><code>aTable: Id, name aColumn: Id, Name, aTable, aDataType aDataType: Id, Name aRow: Id, aTable zBit: Id, aRow, aColumn, Data(Bit) zDateTime: Id, aRow, aColumn, Data (DateTime) zMoney: Id, aRow, aColumn, Data (Money) zNumber: Id, aRow, aColumn, Data (INT) zText: Id, aRow, aColumn, Data (nvarchar(MAX)) </code></pre> <p>Here is some sample data I used to get it up and running:</p> <p><strong>aTable</strong></p> <pre><code>Id Name 1 Users </code></pre> <p><strong>aColumns</strong></p> <pre><code>Id Name aTable aDataType 1 Name 1 2 2 UserId 1 1 3 Occupation 1 2 </code></pre> <p><strong>aDataType</strong></p> <pre><code>Id Name 1 Number 2 Text </code></pre> <p><strong>aRow</strong></p> <pre><code>Id aTable 1 1 2 1 </code></pre> <p><strong>aNumber</strong></p> <pre><code>Id aRow aColumn Data 1 1 1 1245 2 2 2 56 </code></pre> <p><strong>aText</strong></p> <pre><code>Id aRow aColumn Data 1 1 1 Sara 2 2 1 Jake </code></pre> <p>All other z* tables are blank</p> <p>Here is the query to create a dynamic table:</p> <pre><code>select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable] INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable] INNER JOIN [pod].[dbo].[zBit] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow] UNION ALL select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable] INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable] INNER JOIN [pod].[dbo].[zDateTime] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow] UNION ALL select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable] INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable] INNER JOIN [pod].[dbo].[zMoney] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow] UNION ALL select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable] INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable] INNER JOIN [pod].[dbo].[zMoney] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow] UNION ALL select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable] INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable] INNER JOIN [pod].[dbo].[zNumber] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow] UNION ALL select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable] INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable] INNER JOIN [pod].[dbo].[zText] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow] </code></pre> <p>Here is one chunk of this query:</p> <pre><code>select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable] INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable] INNER JOIN [pod].[dbo].[zText] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow] </code></pre> <p>As you can see here a piece of data (z* tables) is identified by a row and a column. When I run this query I get this:</p> <p><strong>Results</strong></p> <pre><code>Table Column DataType Row Data 1 UserId Number 1 1245 1 UserId Number 2 56 1 Name Text 1 Sara 1 Name Text 2 Jake </code></pre> <p>Here, are my desired results: (I am not sure how to turn these rows into columns if the columns are <strong>unknown</strong>)</p> <pre><code>Row UserId Name 1 1245 Sara 2 56 Jake </code></pre> <hr> <p><strong>Big Problem</strong> This table is suppose to have 3 columns remember?</p> <p><strong>aColumns</strong></p> <pre><code>Id Name aTable aDataType 1 Name 1 2 2 UserId 1 1 3 Occupation 1 2 </code></pre> <hr> <p>So my final expected results are:</p> <pre><code>Row UserId Name Occupation 1 1245 Sara NULL 2 56 Jake NULL </code></pre> <p>In the results I need to sort columns as well. Is this even possible. What databases support this kind of functionality. I am wide open to any database that can do this.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USLuke101
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTDuplicate
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODatabase within a database (table design)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODatabase within a database (table design)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODatabase within a database (table design)
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO*I have a need to create a database within a database*. **Why?** What's wrong with simply adding and removing columns and or tables.
      singulars
      1. PODatabase within a database (table design)
      1. USa_horse_with_no_name
    2. COSee http://en.wikipedia.org/wiki/Inner-platform_effect and http://thedailywtf.com/Articles/The_Inner-Platform_Effect.aspx. Why are you reinventing the wheel, as opposed to just having your application dynamically create real rows and columns?
      singulars
      1. PODatabase within a database (table design)
      1. USChris Shain
    3. CO@ChrisShain - The problem with dynamically creating new rows/columns is how do you write your queries, since every query will need to look up which columns exist, and then dynamically allow people to pick what to retrieve. Basically you are creating the framework for a EAV model in the process.
      singulars
      1. PODatabase within a database (table design)
      1. USJames Black
 

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