Rather difficult mySQL problem ...

Hello there,

A friend of mine is having an problem with an big mySQL database and quite a big number of repeating columns over multiple tables.

To describe the problem better, the database serves multiple services like FTP, Postfix, webmail, pretty much all the common services you would be running on a general use Linux server and all of these services are using this database. The problem is that most of the services don't know how to read from multiple tables, so if you use multiple tables entries like username, pasword, switches must be repeated in those service specific tables. hing Ex. UserProfile Table conains all user relevant data. (Username, Password) ACL Table Contains Access related data (AccountEnabled, FTPEnabled, AccessType) FTP Table contains FTP related data (Quota, directories etc) I think you get the picture... So if i need the ftp service I must read the UserProfile Table, The ACL Table and the FTP Table, but since I can't read from multiple tables, I only read the ftp table where I've duplicate the fields I need such as UserName, Password and FTPEnabled.

Now they are trying to create an application to administer all these services, you can imagine that this has been done by hand till now, and the server is serving more than a few hundred users. So, now they are trying to create a administrative interface that manages all those tables. The problem is that 30% of the that must be written is duplicate data. Ex. Username and Password must be written 6 times in different Tables.

Is there a possibility to create a Table with "Symlink" or something similar? So that you have all the data in one Temp Table that is accessible to all services and dynamically updated. Or is there any other "smart" way, you can think of, to make all of this easier?

Thank you in advance for your time, titel

From stackoverflow

• Why can't the services access multiple tables?

  If you really want to "symlink" a table, look into views.

• What you are looking for is called a VIEW.

  That way you can have a central table with all the information an one view per app with the right structure.