Sharing Data accross multiple databases

[jbatliner]

Does anyone know of a way or of a "best practice" to accomplish a scenerio as such:

I have a rather large table that contains data pertaining to books.

I have several otehr databases that need to utilize that information.

What are my options? I would really prefer not to copy the data into each of the tables. If possible I would like to set up some sort of relationship, is that possible?

Thanks!

 

[Ankor]

I don't know if it is a best practice, but if your user have access to all these databases with the same password, you can just take data from the tables you need in the queries:
select * from server.database.owner.table

 

[jbatliner]

You are correct and that does work fine in my applications. I am trying to go the next step and ensure that an incorrect value (Child with out a parent.)is not inserted by some one that is not as familair with the Database as I am.

 

[SQLSister]

I usually set up a view with the inforamtion they need from the table in the other database (that way they don't get access to all the fields, only the ones they might need). You could use a trigger on the insert and update for the tables to ensure the data is consistent.

 

[osjohnm]

Hi

You can't setup constraints across databases, the only way to ensure data integrity is to use triggers.

Another way is to use views which reside in your other databases and then query data from your "books" table(s).

We have a central database with certain tables that are used in all the databases. Each database has to use these tables and has each of the tables setup as view witht he same name eg.

Central DB
user_info - tbl
location - tbl

DB1
user_info - v
location - v
DB2
user_info - v
location - v

You can also limit the permissions on views as well as what is seen by different users.

The other option is to use replication but from what I know of it, it is quite tricky to setup and you have to monitor it like a hawk.

Hope this helps

John

 

[jbatliner]

Can relationships be set-up from a table to a view or would I still need to use triggers?

 

[SQLSister]

Can't set up relationships, so you will need to enforce integrity through triggers.