Synchronizing databases - which method is better?

[enfinity]

Hi *,

I'm in the process of developing a new internal project controlling. Part of the system is a (1) already existing system and (2) an Access front-end. The already existing system uses a MySQL database. The ERD is attached.

Now, I need to integrate both systems and I have a couple practical questions.

(1) I was thinking of using the MySQL database of the already existing system AND add some attributes to the existing tables/add some new tables. I would then access this single database from the existing system and the new system. I'm doing that b/c I need a common user table/project table for reporting purposes. I feel that by doing so I can successfully avoid duplicate records. Now, I was wondering if this approach is common practice?

(2) In case the scenario under (1) is really bad practice, I was thinking of running 2 MySQL databases with 1 user table/project table each. Now I would need to synchronize both tables. What tool would you recommend for this purpose. I was trying out several tools but none fit my needs (that is the tables would probably have a different number of columns, etc.)

Thanks a lot!!!
Steve

 

[Banana]

If you could just clarify one thing for me-

Exactly why do you have a table for users? MySQL already has a table for users. Are the passwords in your users table for accessing MySQL or for this existing system?

Access front-end can be set to use MySQL's security model, or at least your custom user tables, so there should be no need for duplicating and/or synchronizing the users information.

In any cases, I'd much strongly prefer that users data be in one table only.

 

[enfinity]

Hi Banana,

I do need the user table for application reasons. It has nothing to do with user security. So yes, I agree with you: a common user table would be the best way to go! The question now is whether I can accomplish this by doing what I described under (1) or (2) in my initial posting.

 

[Banana]

Ah!

If I'm understanding you now, you're asking whether it's possible if Access can use the same table in the MySQL table for the existing application system. The answer is sure! Access can link to that same user table, and you can use a custom logon form using that table information.

But if you're asking more about whether adding attributes and modifying the user table to work with Access front-end is a good idea, I'd say it depends, just exactly what do you intend to add to it?

If it's just some extra information that has no effect on the existing system, I would go ahead and add the new fields. If it involves editing a column, I would want to tread very very carefully and in any circumstances prefer to keep the table and mold Access to that table than vice versa. Normally, if that table is properly normalized, it won't be a big deal, and you would be largely concerned with data types.

I hope that helps some.