Eric Grau wrote:
JamesDR wrote:
Scott Plumlee wrote:
sol beach wrote:
Why in the world are you trying to keep multiple copies of the data?
Why can't you just access the 'master database'?
On 12/16/05, Scott Plumlee <[EMAIL PROTECTED]> wrote:
I am trying to maintain one master list of countries and the states
within those countries. I'd like to keep this info in a common
database, to be used by 2 or three other databases on the same host,
using foreign keys to make sure that all the country/state data
matches
up to the master list.
If I can't use foreign keys, should I just run regular updates to
sync
the data between duplicate tables of countries and states in each
database, or is there an better method that I'm not seeing?
Sorry, I didn't explain very well.
I have a db I'll call Common. In there, I'd like to keep two
tables, tblCountry and tblState. Then I've got another database,
called Application, with a table called tblAddress.
In the Application database, I'd like to reference tblCountry and
tblStateProvince. When someone fills out form info that is going to
be stored in tblAddress, I want the choices to be pulled from
tblCountry and tblStateProvince.
When I've done this inside only one database, I've always used
foreign keys between the Address.State field and the tblState.State
fields.
My goal IS to only keep one set of country and state data for
multiple databases. But I don't know if I can do that using foreign
keys when things are in two different databases - it doesn't appear
that I can.
So my question is am I better off NOT using foreign keys but doing
everything else the same or using foreign keys and having to
jerryrig some sort of syncing?
If your app is just selecting data and inserting it into fields. Then
have it do something like:
SELECT `ID`,`Stateabv` FROM `Common`.`states` ORDER BY `Stateabv`;
Fields, then when the user saves the data, the ID is written to the
other DB, no syncing needed. I've done something like this in another
app. I'm not sure how foreign keys will work (if they will) but I
*think* you can do a join on this -- tho now we are beyond the scope
of my knowledge :-D
I just did a quick test on my database server and it looks like you
can have a foreign key that references tables in another database.
Using your example databases and tables, you can create the foreign
key by specifying the database name in the REFERENCES clause like this:
ALTER TABLE Application.tblAddress
ADD FOREIGN KEY (State) REFERENCES *Common*.tblState (State);
Eric
Of course without the *. I was trying to make it bold and didn't
realize the mailing list would add them.
Hope this helps,
Eric
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
