Table structure question - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Table structure question

Author

Topic

BoroDrummer
Starting Member

9 Posts

Posted - 2011-05-25 : 11:11:06

I have 3 tables with are structured as grandparent, parent, child. Each of these tables must link to a fourth "Meals" table. A grandparent, parent and child can all have meals. Is there a proper way to do this? I.e.


GrandParent
-----------
ID
Name
MealID

Parent
------
ID
Table1ID (FK GrandParent - ID)
Name
MealID

Child
-----
ID
Table2ID (FK Parent - ID)
Name
MealID

Meals
----------
ID (FK GrandParent - MealID) (FK Parent - MealID) (FK Child - MealID)
Name

Do I really need three Meals tables, one for each grandparent, parent and child table?

I'd like to be able to cascade-delete the Meals records.

Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-25 : 11:19:56

You could do it in a single table - call it person.
You could have a type for grandparent, parent and child or just leave it to the position in the hierarchy.

SO the table is
Person
ID
ParentID
Name
MealID

For GrandParents the ParentID is null or 0 as you wish.

Also have a look at the hierarchy datatype

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BoroDrummer
Starting Member

9 Posts

Posted - 2011-05-25 : 11:24:45

This is a simplified version of a more complex table layout. The Grandparent, parent and child tables are linked to many other tables and contain many unique columns. So although your solution works for the simplified scenario, it won't work for me.

Thanks for trying though!

BoroDrummer
Starting Member

9 Posts

Posted - 2011-05-25 : 11:46:53

Just checked the hierarchy datatype and it appears to only be available in SQL Server 2008. I'm unfortunately stuck with 2005!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-25 : 12:20:08

No you don't need 3 meal tables even with the 3 other tables - not convinced by your reasoning about that but if you can't implement the single table that's a reson for staying with what you have - but if you can't do that I doubt if you will be able to cope with a single meal table either so you probably should stay with what you have until it causes too many problems and you are forced to change.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BoroDrummer
Starting Member

9 Posts

Posted - 2011-05-26 : 07:04:03

Thanks for your help. I've stuck with my original structures, but changed a couple of things. I've removed the foreign key reference in the Meals table and added an OwnerID column. I've written stored procedures to delete the Meals records when a related Grandparent/parent/child record is deleted. This seems to be the only way to do it at the moment. I'm sure I may re-think things when I gain more SQL Server knowledge...

Thanks again!

New structures FYI...

GrandParent
-----------
ID
Name
MealID

Parent
------
ID
Table1ID (FK GrandParent - ID)
Name
MealID

Child
-----
ID
Table2ID (FK Parent - ID)
Name
MealID

Meals
----------
ID
OwnerID (relates to the MealID column in the above tables, but is not a foreign key)
Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources