469,126 Members | 1,291 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

Table design question.

4
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?
Mar 7 '08 #1
4 947
jm1
4
Nothing? Surely this is an easy one... for someone.
Mar 10 '08 #2
ck9663
2,878 Expert 2GB
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
Mar 10 '08 #3
jm1
4
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
_______
Mar 10 '08 #4
jm1
4
[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
_______
Mar 11 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by mr_burns | last post: by
reply views Thread by 2trax | last post: by
15 posts views Thread by kimi | last post: by
4 posts views Thread by yanjie.ma | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.