Table design question.

[jm1]

Longtime lurker... first post. These forums have already helped a lot, so thanks.
I have a design issue that is probably easy to figure out for someone with a little more DB experience than myself. Here is what I'm trying to do:
I have an app that will display several grids which consist of some columns that are specific to a given grid and some that are globally identical among the all grids. The globally identical columns will be displayed for all grids but the relative columns may or may not be a part of a given table...if they do show up in more than one grid, their values will be different. A given row may or may not show up in a given grid as well. You can think of this in terms of a jagged datacube. Here is a simplified version of the design (tableName followed by tableColumns)...

________

Columns
ColumnName | ColumnID(PK)

Rows
RowName | RowID(PK)

Grids
GridName | GridID(PK)

Cells
CellContents | ColumnID(FK) | RowID(FK) | TableID(FK)
_______

I plan to handle the global columns by assigning a GridName of All w/ a GridID of 0. Application logic will treat cells with this GridID as belonging to all grids. This achieves 3NF, but here's the problem- and although the app logic won't allow this to happen it would be possible to duplicate a column in a grid, ie have two columns with the same ColumnName but one contains global data and the other contains data relative to the given grid. This seems like a DB design flaw to me. Any suggestions on how to tidy it up?

[jm1]

Nothing? Surely this is an easy one... for someone.

[ck9663]

Try putting some sample data on those structures and paste it here. Then what you want as your output. Let's go from there...

-- CK

[jm1]

I think I've figured it out. The 'Cells' table should only be a junction table. That means CellContents gets broken out into its own table w/ an Identity value as a PK. The identity value gets inserted in place of CellContents and the idea of using 0 as an "All Grids" identifier goes out the window. Rows that were using 0 as a Grid ID get expanded into individual rows- (1 through NumberOfGrids) and all have the same CellID pointing to the appropriate row in the new table.
If that sounds garbled, its because it is. Back to the second sentence in this post. It's just a junction table. I was thinking too hard in the context of the immediate application. The new approach is more flexible for future applications.

The new design looks like this:

________

Columns
ColumnName | ColumnID(PK)

Rows
RowName | RowID(PK)

Grids
GridName | GridID(PK)

CellJunction
CellID(FK) | ColumnID(FK) | RowID(FK) | TableID(FK)

Cells
CellID(PK) | CellContents
_______

[jm1]

[quote=jm1]I think I've figured it out. The 'Cells' table should only be a junction table. That means CellContents gets broken out into its own table w/ an Identity value as a PK. The identity value gets inserted in place of CellContents and the idea of using 0 as an "All Grids" identifier goes out the window. Rows that were using 0 as a Grid ID get expanded into individual rows- (1 through NumberOfGrids) and all have the same CellID pointing to the appropriate row in the new table.
If that sounds garbled, it probably is. Back to the second sentence in this post- It's just a glorified junction table. I was thinking too hard in the context of the immediate application. The new approach is more flexible for future applications of the same tables.

The new design looks like this:

________

Columns
ColumnName | ColumnID(PK)

Rows
RowName | RowID(PK)

Grids
GridName | GridID(PK)

CellJunction
CellID(FK) | ColumnID(FK) | RowID(FK) | TableID(FK)

Cells
CellID(PK) | CellContents
_______