Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gregwmay
Starting Member
2 Posts |
 Posted - 2009-04-10 : 23:45:57
|
| Hi, I am wondering if there are any recommended design patterns for the following.I have two tables called 'Grid' and 'Subgrid'. A grid may have up to 16 subgrids in a fixed 4x4 array - it is not mandatory for the grid to be full.Design option 1: Grid contains 16 foreign key references to the subgrid table. The problem I see here is that I would need to constrain all 16 references to not equal others (so that the same subgrid cannot be in two locations in the grid).Design option 2: Subgrid contains GridLocationX and GridLocationY columns that are each constrained to the correct dimensions, and also has a uniqueness constraint on these two columns plus the reference to the grid.I think I have answered my own question that option 2 is the best, but interested in any ideas or feedback. |
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-14 : 09:08:25
|
| Have two tables: Grid and SubGrid and then use a junction table to join them together. This is good if your subgrid design has alot of properties. If your subgrid does not have many properties, you can make the subgrid a reference table and link the foreign key to the grid table. You'd put constraints to make sure the same subgrid is not used twice. |
 |
|
|
|
|
|
Design with fixed relationships