MySQL Forums
Forum List  »  MySQL Workbench

Re: Reference from two tables
Posted by: Mike Lischke
Date: June 15, 2009 01:30AM

Rainer,

let me see if I got this right:

1) You have a string table with a PK, which consists of two columns (idString and idLanguage).

2) idString is the lookup key for a string, used in other tables. There can be more than one record with the same idString value (hence the 1:n relationship) but there is not always a record for each language (each possible key/language combination).

3) The language id is taken from a preferences table when querying a string entry.

4) You want now to if you can use two tables (one for the string id and one for the language) to address a record in the string table, right?

If my conclusion is correct then yes, why shouldn't you be able to do that. When you ask for a string do a simple join:

select * from strings, source, prefs
where (strings.idStrings = source.idString) and (strings.idLanguage = prefs.idLanguage);

Since the relationship between strings and prefs is static (only the actual id may change) it makes sense to create a view:

create view StringsInUserLanguage as
select * from strings, prefs where strings.idLanguage = prefs.idLanguage;

and then use this view with your string id only. It will always give you the string in the right language (or null). It might pay off to create two indices (non-unique) on both the idString and the idLanguage column, but that is something you should test.

Hope this was the answer you were looking for.

Mike

Mike Lischke, MySQL Developer Tools
Oracle Corporation

MySQL Workbench on Github: https://github.com/mysql/mysql-workbench
On Twitter: https://twitter.com/MySQLWorkbench
On Slack: mysqlcommunity.slack.com (#workbench)
Report bugs to http://bugs.mysql.com
MySQL documentation can be found here: http://dev.mysql.com/doc/refman/8.0/en/



Edited 1 time(s). Last edit at 06/15/2009 01:31AM by Mike Lischke.

Options: ReplyQuote


Subject
Views
Written By
Posted
2422
June 14, 2009 04:18AM
Re: Reference from two tables
2144
June 15, 2009 01:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.