Обсуждение: combining db's- importing primary keys
I would like to combine multiple databases (same schema) into one master db. Does anyone know how I can reconcile all my primary and foreign keys to maintain referential integrity. Many thanks, Bob
On 2009-07-27, Bob Gobeille <bob.gobeille@hp.com> wrote: > I would like to combine multiple databases (same schema) into one > master db. Does anyone know how I can reconcile all my primary and > foreign keys to maintain referential integrity. prefix them all with something that's unique to their source database. easy to say, hard to do.
On Jul 29, 2009, at 7:02 AM, Jasen Betts wrote: > On 2009-07-27, Bob Gobeille <bob.gobeille@hp.com> wrote: >> I would like to combine multiple databases (same schema) into one >> master db. Does anyone know how I can reconcile all my primary and >> foreign keys to maintain referential integrity. > > prefix them all with something that's unique to their source database. > > easy to say, hard to do. I can't believe this didn't occur to me. All my primary keys are bigint's, but I could add a second field, a unique server number or name, and change my primary (and foreign) keys to the two column combination. This would make it easy for new db's but I need to give some thought to see if there will be any hiccups migrating all the db's. Many thanks Jasen. Bob
Hello, Le 29/07/09 15:02, Jasen Betts a écrit : > On 2009-07-27, Bob Gobeille <bob.gobeille@hp.com> wrote: >> I would like to combine multiple databases (same schema) into one >> master db. Does anyone know how I can reconcile all my primary and >> foreign keys to maintain referential integrity. > > prefix them all with something that's unique to their source database. > > easy to say, hard to do. > A process (and solution) may be different whether: -the master db will replace all other databases (ie. data manipulation would directly perform on data managed by master db); -or the master db will reflect a combined view of data hosted by all other databases (ie. data manipulation would still perform on distributed databases and master db would play a role of federated view); -or both (data manipulation have to be manageable at the both side: on master db and on distributed db). Regards. -- nha / Lyon / France.
On Jul 29, 2009, at 8:26 AM, nha wrote: > Hello, > > Le 29/07/09 15:02, Jasen Betts a écrit : >> On 2009-07-27, Bob Gobeille <bob.gobeille@hp.com> wrote: >>> I would like to combine multiple databases (same schema) into one >>> master db. Does anyone know how I can reconcile all my primary and >>> foreign keys to maintain referential integrity. >> >> prefix them all with something that's unique to their source >> database. >> >> easy to say, hard to do. >> > > A process (and solution) may be different whether: > -the master db will replace all other databases (ie. data manipulation > would directly perform on data managed by master db); > -or the master db will reflect a combined view of data hosted by all > other databases (ie. data manipulation would still perform on > distributed databases and master db would play a role of federated > view); > -or both (data manipulation have to be manageable at the both side: on > master db and on distributed db). In my immediate case, I'm simply combining 7 global databases into a single master. Eventually, I would like to allow our (fossology.org) users to subscribe to data in other databases. That is, allow users to do periodic one-way batch updates from other servers. Bob
Hello, Le 29/07/09 16:44, Bob Gobeille a écrit : > > On Jul 29, 2009, at 8:26 AM, nha wrote: > >> Hello, >> >> Le 29/07/09 15:02, Jasen Betts a écrit : >>> On 2009-07-27, Bob Gobeille <bob.gobeille@hp.com> wrote: >>>> I would like to combine multiple databases (same schema) into one >>>> master db. Does anyone know how I can reconcile all my primary and >>>> foreign keys to maintain referential integrity. >>> >>> prefix them all with something that's unique to their source database. >>> >>> easy to say, hard to do. >>> >> >> A process (and solution) may be different whether: [...cases...] > > In my immediate case, I'm simply combining 7 global databases into a > single master. > > Eventually, I would like to allow our (fossology.org) users to subscribe > to data in other databases. That is, allow > users to do periodic one-way batch updates from other servers. > > Bob In such a purpose (where asynchronous updates seem reasonable), prefixing (as suggested by Jasen Bett) or adding server-based column to current keys (as you mention thereafter) look like handy solutions. Good plan. Regards. -- nha / Lyon / France.
Bob Gobeille wrote: > > On Jul 29, 2009, at 8:26 AM, nha wrote: > >> Hello, >> >> Le 29/07/09 15:02, Jasen Betts a écrit : >>> On 2009-07-27, Bob Gobeille <bob.gobeille@hp.com> wrote: >>>> I would like to combine multiple databases (same schema) into one >>>> master db. Does anyone know how I can reconcile all my primary and >>>> foreign keys to maintain referential integrity. >>> >>> prefix them all with something that's unique to their source database. >>> >>> easy to say, hard to do. >>> >> >> A process (and solution) may be different whether: >> -the master db will replace all other databases (ie. data manipulation >> would directly perform on data managed by master db); >> -or the master db will reflect a combined view of data hosted by all >> other databases (ie. data manipulation would still perform on >> distributed databases and master db would play a role of federated view); >> -or both (data manipulation have to be manageable at the both side: on >> master db and on distributed db). > > In my immediate case, I'm simply combining 7 global databases into a > single master. > > Eventually, I would like to allow our (fossology.org) users to subscribe > to data in other databases. That is, allow > users to do periodic one-way batch updates from other servers. Have you thought about schemas? You can set up different users with different search paths so all the data's in one spot, and getting to another schema is easy enough. No changes necessary to the data itself, just the calling code. -- Postgresql & php tutorials http://www.designmagick.com/
Chris wrote: > Bob Gobeille wrote: >> >> On Jul 29, 2009, at 8:26 AM, nha wrote: >> >>> Hello, >>> >>> Le 29/07/09 15:02, Jasen Betts a écrit : >>>> On 2009-07-27, Bob Gobeille <bob.gobeille@hp.com> wrote: >>>>> I would like to combine multiple databases (same schema) into one >>>>> master db. Does anyone know how I can reconcile all my primary and >>>>> foreign keys to maintain referential integrity. >>>> >>>> prefix them all with something that's unique to their source database. >>>> >>>> easy to say, hard to do. >>>> >>> >>> A process (and solution) may be different whether: >>> -the master db will replace all other databases (ie. data manipulation >>> would directly perform on data managed by master db); >>> -or the master db will reflect a combined view of data hosted by all >>> other databases (ie. data manipulation would still perform on >>> distributed databases and master db would play a role of federated >>> view); >>> -or both (data manipulation have to be manageable at the both side: on >>> master db and on distributed db). >> >> In my immediate case, I'm simply combining 7 global databases into a >> single master. >> >> Eventually, I would like to allow our (fossology.org) users to >> subscribe to data in other databases. That is, allow >> users to do periodic one-way batch updates from other servers. > > Have you thought about schemas? > > You can set up different users with different search paths so all the > data's in one spot, and getting to another schema is easy enough. No > changes necessary to the data itself, just the calling code. Blah, ignore - I didn't read your first email properly :P -- Postgresql & php tutorials http://www.designmagick.com/