MySQL beginner needs help laying out tables -
07-17-2003
, 03:19 PM
I am an absolute novice at databases (MySQL in this case). I want a database
containing translations of sentences between various languages, so I have the
following tables:
CREATE TABLE entries(
entry_nr INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
level_nr INT UNSIGNED NOT NULL
)
CREATE TABLE translations(
entry_nr INT UNSIGNED NOT NULL,
text VARCHAR(255) NOT NULL,
keywords VARCHAR(100),
language_nr INT NOT NULL
)
CREATE TABLE languages(
language_nr INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
name VARCHAR(20)
)
CREATE TABLE levels(
level_nr INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
name VARCHAR(20)
)
The 'entries' table contains just a cross-reference index and a difficulty
level. All the text is contained in the 'translations' table, so I might
insert entries:
entry_nr text keywords language_nr
1 "Hello, my name is William" "" 1
1 "Hola, me llamo William" "" 2
The entry number ties them together. So I want to create a new entry_nr
at the same time that I insert the first translation (eg. english).
Then I use the same entry_nr when I insert the 2nd translation (eg.
spanish).
So:
INSERT into entries (level_nr) VALUES (1);
get the entry_nr 'N' just inserted, then:
INSERT into translations (entry_nr,text,keywords,language_nr) VALUES (N,...);
INSERT into translations (entry_nr,text,keywords,language_nr) VALUES (N,...);
etc
But how do I get 'N' (from PHP in my case) and how do I ensure it is the
one I just created, not that created by someone else at the same time?
I could omit the entries table altogether and make entry_nr in the
translations table an auto-incremented primary key. Then add a
cross-reference field to the translations table to tie translations
together. But this seems inelegant.
I wonder if someone would steer me in the right direction.
Thanks in advance for your attention and help.
Regards
William Morris |