+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Ideas for database structure / Dictionary (/showthread.php?tid=3576)
Ideas for database structure / Dictionary - El Forum - 10-11-2007
[eluser]trice22[/eluser]
Hello and sorry, if this a bit of a vague topic, but I'm in the early stages of a new project and not really experienced with the development of database structures—so every input would be very welcome.
Here's what I want to do:
I'm planning on a small app. that will work a little like a dictionary. That means I've at least two languages (let's say English and German) and vocabulary for both of them.
That would call for two tables:
Code:
English with ID and word and
German with ID and wordAnd since one word (like "to put") can have more than one meaning ("setzen", "stellen", legen") I'll need a third table for the relations between table "English" and "German".
Something like
Code:
ID, ID_eng, ID_gerAm I correct so far? Is there a better way to handle something like this?
Now here's my bigger problem:
The amount of languages is dynamic. Let's say, somebody could add French or Chinese.
How would I handle the relations then? If I'd have an extra table for each relation I'll very soon reach a gigantic amount of tables. There gotta be a better way.
Any ideas, links or general input to the dictionary-in-a-database topic?
I'm in the planning stage and very grateful for every idea.
Thanks a lot,
-trice
P.S.: I know this is not necessarily CI related, but since the app. is going to be developed with CI and everyone in here always very helpful, I'm still going to give it a shot.
Ideas for database structure / Dictionary - El Forum - 10-11-2007
[eluser]Michael Wales[/eluser]
rather than make a table for each new relation, you could always make a field in the English table (ie. let's name it german_id) that would be used as a key to point to the german equivalent words. Maybe a comma-delimited list.
Either way, you have the basic gist of it down.
Ideas for database structure / Dictionary - El Forum - 10-11-2007
[eluser]trice22[/eluser]
But wouldn't a comma-delimited list not be pretty slow to search through (I'm planning on using MySQL, btw.)?
(—It's not a rhetorical question, I really don't know.)
Thanks for the fast reply, anyways. - trice
Ideas for database structure / Dictionary - El Forum - 10-11-2007
[eluser]Michael Wales[/eluser]
It would be a lot faster to perform a join across multiple tables, I was just trying to throw out a few more options. If you were worried about table clutter in your phpMyAdmin.
Ideas for database structure / Dictionary - El Forum - 10-11-2007
[eluser]trice22[/eluser]
Well, yeah - that's one reason (the mess in phpMyAdmin :cheese: ) - my main problem is, that I have no idea how big the whole thing might become. If you consider only 5 languages (which means 15 join-tables, right?) with 500.000 entries each, that's already quite a package. That's the reason why I'm trying to find a "future"-proof way to handle this.
[quote author="walesmd" date="1192137941"]It would be a lot faster to perform a join across multiple tables,[...][/quote]
In this case you refer to the technique I was asking about? one table "in between" two language tables?
Man, I gotta apologize for my language again—I don't consider myself to be developer and I've learned a little about relational databases at the university, but basically everything I'm doing here is a bit foggy :red: .
Anyone experience with dictionaries online itself?
Thanks a lot already,
-trice
Ideas for database structure / Dictionary - El Forum - 10-11-2007
[eluser]Michael Wales[/eluser]
Quote:In this case you refer to the technique I was asking about? one table “in between” two language tables?
Yes, but as you mentioned - the number of tables will be growing exponentially with each new language you add. It's definitely a unique problem, it would be interesting to see how others would handle this situation.
Ideas for database structure / Dictionary - El Forum - 10-11-2007
[eluser]trice22[/eluser]
[quote author="walesmd" date="1192146133"]it would be interesting to see how others would handle this situation.[/quote]
Well said! bring it on :cheese:
Anyone?...
-trice
Ideas for database structure / Dictionary - El Forum - 10-11-2007
[eluser]Crafter[/eluser]
Quote:The amount of languages is dynamic. Let’s say, somebody could add French or Chinese.
Then perhaps consider a single table multilinguage_dictionary (phrase, translated_phrase, source_lang, translated_language).
You would then have:
'to put", "setzen" ,"en", "de"
'to put", “stellen”, "en", "de"
'to put", “legen", "en", "de"
You'd have to perform some magic with indexes,and you'd probably have to use construct like database views. You'd probably have move in both directions to lookup.
Man it could be a performance nightmare, but your code will be as smooth as silk
Perosnally, i don't think using a database for lexicon applications is the right way. I'd program this in a language like C, where I'd probably have to build the database structures in the heap (using pointers).
Ideas for database structure / Dictionary - El Forum - 10-11-2007
[eluser]trice22[/eluser]
[quote author="Crafter" date="1192150241"]
Perosnally, i don't think using a database for lexicon applications is the right way. I'd program this in a language like C, where I'd probably have to build the database structures in the heap (using pointers).[/quote]
I was really afraid, someone would say something like this. Unfortunately that's way out of my league. I think the single table idea looks interesting–thanks for that–but I consider the performance a quite important factor, since I don't know how many languages the DB will hold in the end.
I don't know–maybe I'll just give it a try and see what happens, if I let the app dynamically create the join-tables. It can't be that bad as long as I don't have to maintain it by hand/phpMyAdmin, can it? And I guess it's still the fastest solution.
Well, still—if someone has some more ideas,... I'm open for an some genius input.
-trice
Ideas for database structure / Dictionary - El Forum - 10-11-2007
[eluser]Crafter[/eluser]
Well, you could always try the sticky-tape-and-paper-clip solution.
For example, create a tag soup type entry of all related words in a single field. including all transacriptions/transaltiona and a mapping
transcription : ‘to put” , “setzen” , “stellen”, “legen”
translation : "en", "de", "de", "de"
In this way you could search for the word and read the correspoding mapping.
Again, you will have to weigh the benefit of a string-based index field and and regex use against your table join method.
You could hash the values, I read somewhere that MySQL automatically uses a hash index, but this only happens under certain conditions,