dbTalk Databases Forums  

MySQL beginner needs help laying out tables

comp.databases comp.databases


Discuss MySQL beginner needs help laying out tables in the comp.databases forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
William Morris
 
Posts: n/a

Default 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

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.