Table design - one to many - 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

General SQL Server Forums

New to SQL Server Programming

Table design - one to many

Author

Topic

caseybasichis
Starting Member

5 Posts

Posted - 2012-03-11 : 12:56:54

Hi,


0001(0002,0003)
0003(0004,0005)

ID      Class       Parent  Param#
0005    Thimbal     0003    2
0004    Needle      0003    1
0003    SewingKit   0001    2
0002    Soap        0001    1
0001    SuitCase    root    0

I have system like the above, where an Orm (QxOrm) is storing tens of thousands of dependency injection chains. My plain is to use the id's to recursively build out the chain so I can start at 0001 and have it automatically build 0002-0005. QxOrm has a built in introspection factory that lets me create new objects at runtime by supplying the class name.

What is the best way to give the parent information about what its children are (as opposed to the opposite of nodes only knowing their parent asabove). There maybe be any number of children to one parent. The way above seems much cleaner, but apparently make recursively creating the chain much more difficult.

I could serialize all the children id's into a string to exist in one column, but that's not terribly ideal right? Like "0002;0004;0066"

I could also make a new relation table where every child is tied to every parent, but then Im going to end up with one huge table with 10-100 millions values in it.

I am very new too all of this so if there is an obvious solution I would be very pleased to hear it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-11 : 13:07:11

i think what you should be using is recursive logic using common table expressions for this

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

caseybasichis
Starting Member

5 Posts

Posted - 2012-03-11 : 13:14:40

Thanks for the reply.

I don't think I will be able to take the approach.

I the long term I intended to use remote and local databases, but in the early going this is going to be wrapped around SQLite which doesn't seem to support Recursive CTE.

What other approaches might I take?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-11 : 14:19:42

then you have to go for looping logic as detailed below

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109492

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

caseybasichis
Starting Member

5 Posts

Posted - 2012-03-11 : 15:16:03

Thank you for your help and that link. That definitely gets me on my way toward getting this project underway.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-11 : 15:26:24

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources