 | |
#1
 
| |||
| |||
|
Modeling question... -
07-08-2008
, 11:25 AM
#2
| |||
| |||
|
|
Hi! Not sure if this is the right group but I've come across a problem I'm at a loss to model properly. Here's the setup: A model contains three entities ("Level"s) describing projects: - Project Family records, each referencing several - Project records (with different attributes), in turn referencing - Sub projects, with different attributes again. Those three Project levels are connected by straight forward 1 to n relationships. But the problem is that they all have a bunch of key/value pairs. So, a project family can have a key/value-pair StartDate=20080615. But each project and subproject can have a different StartDate. On the other hand, sub projects, projects and family don't need to have the same key/value pairs. Now, the simple solution is to have three key_value tables and be done with it. However what I'd very much prefer is just one key_value table with some kind of "level" attribute, 0 being family, 1 being project and 2 being sub project. The primary keys on the level entities are all numeric so this would perhaps work but I have no idea how to get the foreign key constraints done because the key_value table would have three parents. How does one model relationships in which - one child table has several parent tables - each parent record can refer to several child records - each child record belongs to exactly one parent record in exactly one of the several parent tables? Is there a declarative way to enforce consistency? Lots of Greetings! Volker |
#3
| |||
| |||
|
|
Hi! Not sure if this is the right group but I've come across a problem I'm at a loss to model properly. Here's the setup: A model contains three entities ("Level"s) describing projects: - Project Family records, each referencing several - Project records (with different attributes), in turn referencing - Sub projects, with different attributes again. Those three Project levels are connected by straight forward 1 to n relationships. But the problem is that they all have a bunch of key/value pairs. So, a project family can have a key/value-pair StartDate=20080615. But each project and subproject can have a different StartDate. On the other hand, sub projects, projects and family don't need to have the same key/value pairs. Now, the simple solution is to have three key_value tables and be done with it. However what I'd very much prefer is just one key_value table with some kind of "level" attribute, 0 being family, 1 being project and 2 being sub project. The primary keys on the level entities are all numeric so this would perhaps work but I have no idea how to get the foreign key constraints done because the key_value table would have three parents. How does one model relationships in which - one child table has several parent tables - each parent record can refer to several child records - each child record belongs to exactly one parent record in exactly one of the several parent tables? Is there a declarative way to enforce consistency? Lots of Greetings! Volker |
#4
| |||
| |||
|
|
Hi! Not sure if this is the right group but I've come across a problem I'm at a loss to model properly. Here's the setup: A model contains three entities ("Level"s) describing projects: - Project Family records, each referencing several - Project records (with different attributes), in turn referencing - Sub projects, with different attributes again. Those three Project levels are connected by straight forward 1 to n relationships. But the problem is that they all have a bunch of key/value pairs. So, a project family can have a key/value-pair StartDate=20080615. But each project and subproject can have a different StartDate. On the other hand, sub projects, projects and family don't need to have the same key/value pairs. Now, the simple solution is to have three key_value tables and be done with it. However what I'd very much prefer is just one key_value table with some kind of "level" attribute, 0 being family, 1 being project and 2 being sub project. The primary keys on the level entities are all numeric so this would perhaps work but I have no idea how to get the foreign key constraints done because the key_value table would have three parents. How does one model relationships in which - one child table has several parent tables - each parent record can refer to several child records - each child record belongs to exactly one parent record in exactly one of the several parent tables? Is there a declarative way to enforce consistency? Lots of Greetings! Volker |
#5
| |||
| |||
|
|
Hi! Not sure if this is the right group but I've come across a problem I'm at a loss to model properly. Here's the setup: A model contains three entities ("Level"s) describing projects: - Project Family records, each referencing several - Project records (with different attributes), in turn referencing - Sub projects, with different attributes again. Those three Project levels are connected by straight forward 1 to n relationships. But the problem is that they all have a bunch of key/value pairs. So, a project family can have a key/value-pair StartDate=20080615. But each project and subproject can have a different StartDate. On the other hand, sub projects, projects and family don't need to have the same key/value pairs. Now, the simple solution is to have three key_value tables and be done with it. However what I'd very much prefer is just one key_value table with some kind of "level" attribute, 0 being family, 1 being project and 2 being sub project. The primary keys on the level entities are all numeric so this would perhaps work but I have no idea how to get the foreign key constraints done because the key_value table would have three parents. How does one model relationships in which - one child table has several parent tables - each parent record can refer to several child records - each child record belongs to exactly one parent record in exactly one of the several parent tables? Is there a declarative way to enforce consistency? Lots of Greetings! Volker |
#6
| |||
| |||
|
|
Hi! Not sure if this is the right group but I've come across a problem I'm at a loss to model properly. Here's the setup: A model contains three entities ("Level"s) describing projects: - Project Family records, each referencing several - Project records (with different attributes), in turn referencing - Sub projects, with different attributes again. Those three Project levels are connected by straight forward 1 to n relationships. But the problem is that they all have a bunch of key/value pairs. So, a project family can have a key/value-pair StartDate=20080615. But each project and subproject can have a different StartDate. On the other hand, sub projects, projects and family don't need to have the same key/value pairs. Now, the simple solution is to have three key_value tables and be done with it. However what I'd very much prefer is just one key_value table with some kind of "level" attribute, 0 being family, 1 being project and 2 being sub project. The primary keys on the level entities are all numeric so this would perhaps work but I have no idea how to get the foreign key constraints done because the key_value table would have three parents. How does one model relationships in which - one child table has several parent tables - each parent record can refer to several child records - each child record belongs to exactly one parent record in exactly one of the several parent tables? Is there a declarative way to enforce consistency? Lots of Greetings! Volker |
#7
| |||
| |||
|
|
Hi! Not sure if this is the right group but I've come across a problem I'm at a loss to model properly. Here's the setup: A model contains three entities ("Level"s) describing projects: - Project Family records, each referencing several - Project records (with different attributes), in turn referencing - Sub projects, with different attributes again. Those three Project levels are connected by straight forward 1 to n relationships. But the problem is that they all have a bunch of key/value pairs. So, a project family can have a key/value-pair StartDate=20080615. But each project and subproject can have a different StartDate. On the other hand, sub projects, projects and family don't need to have the same key/value pairs. Now, the simple solution is to have three key_value tables and be done with it. However what I'd very much prefer is just one key_value table with some kind of "level" attribute, 0 being family, 1 being project and 2 being sub project. The primary keys on the level entities are all numeric so this would perhaps work but I have no idea how to get the foreign key constraints done because the key_value table would have three parents. How does one model relationships in which - one child table has several parent tables - each parent record can refer to several child records - each child record belongs to exactly one parent record in exactly one of the several parent tables? Is there a declarative way to enforce consistency? Lots of Greetings! Volker |
#8
| |||
| |||
|
|
Hi! Not sure if this is the right group but I've come across a problem I'm at a loss to model properly. Here's the setup: A model contains three entities ("Level"s) describing projects: - Project Family records, each referencing several - Project records (with different attributes), in turn referencing - Sub projects, with different attributes again. Those three Project levels are connected by straight forward 1 to n relationships. But the problem is that they all have a bunch of key/value pairs. So, a project family can have a key/value-pair StartDate=20080615. But each project and subproject can have a different StartDate. On the other hand, sub projects, projects and family don't need to have the same key/value pairs. Now, the simple solution is to have three key_value tables and be done with it. However what I'd very much prefer is just one key_value table with some kind of "level" attribute, 0 being family, 1 being project and 2 being sub project. The primary keys on the level entities are all numeric so this would perhaps work but I have no idea how to get the foreign key constraints done because the key_value table would have three parents. How does one model relationships in which - one child table has several parent tables - each parent record can refer to several child records - each child record belongs to exactly one parent record in exactly one of the several parent tables? Is there a declarative way to enforce consistency? Lots of Greetings! Volker |
#9
| |||
| |||
|
|
Hi! Not sure if this is the right group but I've come across a problem I'm at a loss to model properly. Here's the setup: A model contains three entities ("Level"s) describing projects: - Project Family records, each referencing several - Project records (with different attributes), in turn referencing - Sub projects, with different attributes again. Those three Project levels are connected by straight forward 1 to n relationships. But the problem is that they all have a bunch of key/value pairs. So, a project family can have a key/value-pair StartDate=20080615. But each project and subproject can have a different StartDate. On the other hand, sub projects, projects and family don't need to have the same key/value pairs. Now, the simple solution is to have three key_value tables and be done with it. However what I'd very much prefer is just one key_value table with some kind of "level" attribute, 0 being family, 1 being project and 2 being sub project. The primary keys on the level entities are all numeric so this would perhaps work but I have no idea how to get the foreign key constraints done because the key_value table would have three parents. How does one model relationships in which - one child table has several parent tables - each parent record can refer to several child records - each child record belongs to exactly one parent record in exactly one of the several parent tables? Is there a declarative way to enforce consistency? Lots of Greetings! Volker |
#10
| |||
| |||
|
|
Hi! Not sure if this is the right group but I've come across a problem I'm at a loss to model properly. Here's the setup: A model contains three entities ("Level"s) describing projects: - Project Family records, each referencing several - Project records (with different attributes), in turn referencing - Sub projects, with different attributes again. Those three Project levels are connected by straight forward 1 to n relationships. But the problem is that they all have a bunch of key/value pairs. So, a project family can have a key/value-pair StartDate=20080615. But each project and subproject can have a different StartDate. On the other hand, sub projects, projects and family don't need to have the same key/value pairs. Now, the simple solution is to have three key_value tables and be done with it. However what I'd very much prefer is just one key_value table with some kind of "level" attribute, 0 being family, 1 being project and 2 being sub project. The primary keys on the level entities are all numeric so this would perhaps work but I have no idea how to get the foreign key constraints done because the key_value table would have three parents. How does one model relationships in which - one child table has several parent tables - each parent record can refer to several child records - each child record belongs to exactly one parent record in exactly one of the several parent tables? Is there a declarative way to enforce consistency? Lots of Greetings! Volker |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.