473,327 Members | 2,007 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Setting up a convoluted database

Hello!

I am not a complete new beginner, but until now I gathered the information I needed from where I found it. However, my newest problem requires more than that.
I want to create a table system, in which one table is dependent on the other, namely: a database of vocabulary and a corresponding database of languages.
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE `language` ( 
  2. `language` varchar(255) NOT NULL default '', 
  3. `specialcharacters` varchar(255) NOT NULL default '', 
  4. CREATE TABLE `vocabulary` ( 
  5. `id` int(11) unsigned NOT NULL auto_increment, 
  6. `language` varchar(255) character set utf8 NOT NULL default '', 
  7. `known` text character set utf8 NOT NULL, 
  8. `unknown` text character set utf8 NOT NULL, 
  9. PRIMARY KEY (`id`) 
  10. ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 
  11.  
This is a simplified version, but it ought to illustrate the relation I'm going to explain:

The "vocabulary" table contains rows of vocabulary with different properties, one of which is their language.
The "language" table is supposed to be dependent on the "vocabulary" table.
Whenever a row with a new language is created in the "vocabulary" table, a new row with the new language should be created in the "language" table.
If the last row of a language is deleted (or updated to have another language) in "vocabulary", then the corresponding row in "languages" should be deleted as well.

Now I've thought of different approaches to apply this ruleset:
1. With PHP. I could simply define clauses in my web application that do those things. It would be pretty complicated, messy and I would have to do it again and again, when I add features.
2. By forgetting about a separate table and simply integrating the languages in the "vocabulary" table. However, the properties of the different languages have to be saved somewhere and it would mean an immense redundant data load, if I integrated the same information into rows again and again.
3. With Triggers. I read up on this subject, because I was told, that this is was I needed. I came to the conclusion that triggers can't solve this issue, but I'd be happy to be proven wrong.
4. This is why I asked. Are there other possibilites? I was told about cascading deletes, but those would constitute the reverse of what I'm trying to do, wouldn't they?

I'm working on a web application, that accesses MySQL 5.0 via PHP 5.

Thanks a lot for all replies,
Ruben
May 20 '07 #1
5 1431
pbmods
5,821 Expert 4TB
Couple of options for you:
As for checking in your PHP code... well, that's kind of necessary anyway. After all, you will need to validate input before committing it to the database; might as well add a 'does a matching language id exist?' to the list?

If you objectify your input validation, you'd only need to write it once, and then call (e.g.,) a static function that actually does the validation. Combine that with a simple __autoload defined in your init page, and you will notice little or no 'code bloat'.
May 20 '07 #2
Couple of options for you:
I read about this, but I also heard, that InnoDBs have many downsides. I'm currently using my MyISAM. Can you tell me, what I'll lose in return for gaining the possibility of foreign key constraints?
I'm using MySQL 5.0 and my hoster recently denied me the super privilege that I would need, because I am on a shared host.

As for checking in your PHP code... well, that's kind of necessary anyway. After all, you will need to validate input before committing it to the database; might as well add a 'does a matching language id exist?' to the list?
Yes, but it would be a bit annoying, because there are so many opportunities for this to change. And I'm a big fan of simplicity ^^

If you objectify your input validation, you'd only need to write it once, and then call (e.g.,) a static function that actually does the validation. Combine that with a simple __autoload defined in your init page, and you will notice little or no 'code bloat'.
Sadly, I know too little about object-oriented programming to do that.

I'll read up on foreign key constraints, thanks a lot!
May 20 '07 #3
pbmods
5,821 Expert 4TB
I read about this, but I also heard, that InnoDBs have many downsides. I'm currently using my MyISAM. Can you tell me, what I'll lose in return for gaining the possibility of foreign key constraints?
Take a look at this page for an overview:
Storage Engine Architectures, Part 3

Sadly, I know too little about object-oriented programming to do that.
Fair enough. At the least, what you can do is define a function in a separate file, then include it in any script that needs to use it.

E.g., create file validator.php:
Expand|Select|Wrap|Line Numbers
  1. /**
  2. *
  3. * validateLanguage
  4. *
  5. * Checks for valid data before allowing insertion in to DB.
  6. *
  7. * @param array $data Array of fields/values to be inserted.
  8. *
  9. * @return bool|array Returns true if data is valid, or an array containing the invalid field/value pairs.
  10. *
  11. */
  12. function validateVocab($data) {
  13.     //  Check for valid $data, including a check to see if there is a matching row in `Languages`.  Return true if all data is valid, or else create an array containing the fields/values that didn't pass validation
  14. }
  15.  
And then when you need to use it:
Expand|Select|Wrap|Line Numbers
  1. include('validator.php');
  2.  
  3. .
  4. .
  5. .
  6.  
  7. $newVocabulary = array(
  8.     'language' => $_POST['language'],
  9.     etc.
  10. );
  11.  
  12. if(($errs = validateVocab($newVocabulary)) !== true) {
  13.     throw new Exception(print_r($errs, true));
  14. }
  15.  
You'll probably want to do something other than throw an exception in your code, though.

When you invoke validateVocab, you want to capture the output in case it's an array (which, incidentally, will == true, but won't === true).

If the output === true, then you know that $newVocabulary is OK, so you can commit it to the database. Otherwise, you use $errs to check to see which fields were invalid and go from there.
May 20 '07 #4
Thank you! I looked at the comparison on Wikipedia as well. I think the fact, that InnoDB needs more space, is the most important downside for me.

I'll have to calculate, whether it would be worse to lose storage space or to need more time processing (would a check with PHP+MySQL, e.g. "whenever the language of a vocabulary is updated, deleted or created, then check whether the corresponding language entry exists and delete or create the row" constitute a big strain on the speed?)

This is what I know the least about:
Is it actually so much more effective to use the advanced functions and features of MySQL or am I equally well off when I just rely on PHP for anything that goes beyond SELECT, UPDATE, DELETE, INSERT (drastically speaking)?
May 20 '07 #5
pbmods
5,821 Expert 4TB
I'll have to calculate, whether it would be worse to lose storage space or to need more time processing (would a check with PHP+MySQL, e.g. "whenever the language of a vocabulary is updated, deleted or created, then check whether the corresponding language entry exists and delete or create the row" constitute a big strain on the speed?)
This is what I know the least about:
Is it actually so much more effective to use the advanced functions and features of MySQL or am I equally well off when I just rely on PHP for anything that goes beyond SELECT, UPDATE, DELETE, INSERT (drastically speaking)?
Theoretically, you're better off using MySQL's stuff, since it's faster for MySQL to, for example, execute a trigger before/after an INSERT than to set up an additional MySQL query in PHP.

Realistically, unless you're operating on hundreds of rows at a time, the difference in speed will be negligible (this should answer both of your questions).
May 21 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

21
by: Michael Bierman | last post by:
Please forgive the simplicy of this question. I have the following code which attempts to determine the color of some text and set other text to match that color. It works fine in Firefox, but does...
18
by: Dixie | last post by:
Can I set the Format property in a date/time field in code? Can I set the Input Mask in a date/time field in code? Can I set the Format of a Yes/No field to Checkbox in code? I am working on...
6
by: Jordan | last post by:
I'm working with a Repeater using a custom function inside the <itemtemplate> to return a text string. The parameters SHOULD be (DateTime, DateTime, Int16) but the difficulties in getting DateTimes...
22
by: Chris Moltisanti | last post by:
Hey, I have a DIV and I want to dynamically set its innerHTML. I know I can set it by doing the following myDiv.innerHTML = '<img src=\"myImage.gif\">' However, the html that I want to set in...
5
by: gesres | last post by:
I have some column headings whose widths are set via styles: <TR> <TD style="visibility:hidden;width:0px">&nbsp;</TD> <TD style="visibility:hidden;width:0px">&nbsp;</TD> <TD...
1
by: laredotornado | last post by:
Hi, I'm using PHP 4.4.4 on Apache 2 on Fedora Core 5. PHP was installed using Apache's apxs and the php library was installed to /usr/local/php. However, when I set my "error_reporting"...
6
by: metaperl | last post by:
I would like to check the setting of this variable in our MS-SQL 2000 database. Also, is there info on what the default value of this variable is?
1
by: kingdombrokers | last post by:
Hello Guru's I am new to db2 and need some help with setting up a new database. So far I have created the database. "DB2 create database" And was sent the .sql file would this be the DDL? At...
0
by: kishjeff | last post by:
Hi. This is a slightly convoluted question, but maybe it is obvious.. <convolutedquestion> I'd like to search a set of xml documents and get the element name and its id attribute of...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.