3

I have an application that displays tabular information and allows the user to enter and edit it. I have been working on the database design for this and have come across the following problem.

I want to avoid creating an MSSQL table for each table in the application, so I've been trying to represent all of the tables together, in two tables.

I have defined a MyAppTable table which has Id INTEGER primary key and a Name CHARACTER VARYING(255) columns.

From there, I have defined a MyAppColumn table which foreign keys against the MyAppTable.Id column and also has a Position INTEGER column to indicate its position in the rendered table.

Finally, I have defined a MyAppRow table which foreign keys against the MyAppColumn.Id column and has a Data BINARY(32) column. It's a table of small files, so this data type is fitting.

The actual problem is that I cannot guarantee that the MyAppColumn.Position column will be unique for each MyAppTable.

An example of a normal situation:

  • MyAppTable with Id 0
  • MyAppColumn with Id 0, TableId 0, Position 0
  • MyAppColumn with Id 1, TableId 0, Position 1
  • MyAppRow with ColumnId 0, Data [something]
  • MyAppRow with ColumnId 1, Data [something]
  • MyAppTable with Id 1
  • MyAppColumn with Id 2, TableId 1, Position 0
  • MyAppRow with ColumnId 2, Data [something]

An example of a situation I want to make impossible in the database:

  • MyAppTable with Id 0
  • MyAppColumn with Id 0, TableId 0, Position 0
  • MyAppColumn with Id 1, TableId 0, Position 0

As you can see, I want some way of constraining a pair of columns. The two columns TableId and Position should be UNIQUE but if that were done independently, it would mean two tables could never have columns both at a Position of 0.

How can I accomplish this in Microsoft SQL Server 2012?

Michael J. Gray
  • 9,784
  • 6
  • 38
  • 67
  • 2
    This is a **really really really really really really really really really really really really really really really really really really really really really really really really really really really really really really really really really really really really** terrible design pattern, especially if the only benefit is you don't have to bother creating a bunch of tables to represent tables. – JNK Nov 07 '12 at 21:30
  • 1
    why on earth would you design your database this way? You are asking for a whole lot of trouble with this design. This is way too complicated, if you get hit by a bus no one will have any clue what you are doing. – Taryn Nov 07 '12 at 21:30
  • Maybe if you explain more what your application is doing, provide some sample data, etc we can help you come up with a better database design. I cannot even imagine how you would query it. – Taryn Nov 07 '12 at 21:34
  • Hm, I didn't expect this much negative feedback. I honestly don't think it's **that** bad of a design. Especially after speaking with a coworker about it. Basically, we're displaying a grid of files, each file needs to be in a specific column and on a specific row for the system to work properly. We want to represent that in the database. Each grid is pretty much like a table. Each row is really a row and each column is really a column. My first thought was to create real tables for each grid we plan to represent, but it's unmaintainable; besides, they're all the same structure. – Michael J. Gray Nov 07 '12 at 21:43
  • @MichaelJ.Gray Will the data for each item in each grid be basically the same (they are all file names or whatever)? If it's just a grid you need then you may want to do a coordinate system instead – JNK Nov 07 '12 at 21:45
  • @MichaelJ.Gray And don't take the negative feedback personally, this is a relatively common design anti-pattern. It seems clever and a good idea at first but a ways into the implementation process you will normally wish you had used actual tables. – JNK Nov 07 '12 at 21:46
  • @JNK I always take negative feedback for what it is, open criticism. It's always great to learn a better way to do something. I really appreciate everyone taking the time to respond. Yes, all of the data will be basically the same structure. It's all a bunch of PNG files stored as binary content. One thing I've been thinking of is actually having a table for each of the grids and then a table for the entries and then making a table for the columns which foreign keys against the grid it belongs to and the data it belongs to. A 3 column composite key would constrain the position too. – Michael J. Gray Nov 07 '12 at 21:51
  • @MichaelJ.Gray See the edit to my answer with a possible solution. – JNK Nov 07 '12 at 21:51
  • @JNK http://pastie.org/5342678 how about something like this? – Michael J. Gray Nov 07 '12 at 21:56
  • @MichaelJ.Gray I think a coordinate system would be easier to maintain than keeping a separate table of columns and rows. – JNK Nov 07 '12 at 21:57
  • Have a look at [this article](http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/). – Robert Harvey Nov 08 '12 at 22:10

1 Answers1

6

The solution to your problem is to not encode metadata in tables but to create actual tables, even if it's inconvenient or a lot of work.

Some stuff that will bite you in the rear very soon:

  • No relationships can be modeled
  • No way to index
  • No input validation (is that a date or an int or an image?)
  • No way to get actual accurate statistics
  • Every single query that you write will be a nightmare

Save yourself a lot of time and effort in the future and just make actual tables now in the design phase. This type of meta-data "god table" has been tried repeatedly and never works.

That being said, if you are representing a very limited set of data (like you say, for files only), I would consider a coordinate system.

Keep your MyAppTable as is.

Create a MyAppFile table with fields:

  • FileId (int identity)
  • MyAppTableId (FK)
  • Data (varbinary)
  • XPos (int)
  • YPos (int)

With a unique constraint on (MyAppTableId, Xpos, Ypos).

This more accurately represents what you are trying to store, will be easier to maintain, and allows you to enforce integrity.

JNK
  • 63,321
  • 15
  • 122
  • 138
  • Marking this one as the solution since it's an exact representation of what we are displaying. It seems like the simple solutions are the right ones usually. Thanks again. – Michael J. Gray Nov 07 '12 at 22:00
  • One thing this didn't do though is support the requirement of naming columns in the grid. We've added that by making a separate table of column names, since they're going to be the same across every grid. We've also foreign keyed the column position against the column names table, so that every column will always have a name. – Michael J. Gray Nov 07 '12 at 23:20