Arrays & "Lookup Tables"

[jrotunda85]

I have set up a DB which uses a Code table to store numerical values that correspond to a "Description" of the cigar. For example, a Cigar made in the Dominican Republic would be stored in the DB as being made in "80004." I'm not completely immovable on the Code table if someone has a better idea but my understanding is that storing values as numbers is more effecient and makes queries run quicker than text strings.

In any case, I am developing a page which displays all the information for a given cigar individually. This is what the sections look like currently:

Header Code

<?php
session_start();

//Set Page Variable
$CigarID = $_GET['cigar_id'];

//Open Database
include 'dbconnection.php';

//Set Array
$SQL = "SELECT * from Cigar WHERE Cigar_ID = $CigarID";
$Result = mysql_query($SQL);
$Array = mysql_fetch_array($Result);

//Decode Manufacturer
$SQL2 = "SELECT * FROM Code WHERE $Array[3] = Code";
$Result2 = mysql_query($SQL2);
$Array2 = mysql_fetch_array($Result2);

//Decode Wrapper
$SQL3 = "SELECT * FROM Code WHERE $Array[4] = Code";
$Result3 = mysql_query($SQL3);
$Array3 = mysql_fetch_array($Result3);
?>

Sample of Display Text

Cigar Id = <?php echo $CigarID ?><br>
Cigar Description = <?php echo $Array[2] ?><br>
Cigar Manufacturer = <?php echo $Array2[2] ?><br>
Cigar Wrapper = <?php echo $Array3[2] ?><br>

As you can see, the only way I've been able to get it to work is by creating a set of commands for EACH descriptor of the cigar that has a value stored in the Code table. This is frankly annoying and I'm sure there has to be another, more efficient, way to do this but I can't figure it out. I've tried setting up the Code table itself as an array but then it's having problems identifying the individual descriptors (e.g. Wrapper, Binder Filler, etc) to link them to the Code table.

Any help or suggestions??? :confused:

[pritaeas]

If you create a separate table for manufacturers, wrappers, etc. then it is possible to retrieve everything with a single query. It may be possible now if I look into it, but it is not the most ideal situation.

[jrotunda85]

If you create a separate table for manufacturers, wrappers, etc. then it is possible to retrieve everything with a single query. It may be possible now if I look into it, but it is not the most ideal situation.

In an ideal world I would like to keep the tables to a minimum as there are a lot of things that need to be stored and that are relatively unchanging.

[smantscheff]

In the long run it pays to have your data as structured and un-redundant as possible. So I support pritaeas' advice to use lookup tables for coded values.
Regarding efficiency and performance, with properly set indexes you will not notice any difference if you use text strings or code numbers - at least not if your row count stays below a million or so. Therefore it might be easier to store each string explicitly, but with a foreign key reference pointing to a lookup table so that you cannot store 'uncontrolled' string values.

[pritaeas]

Then you could nest your queries. The query would get very long, but you'd only need one.

What would be the gain by having only one table ? Lookup tables are very common.

And what smantscheff said ;)

[jrotunda85]

In the long run it pays to have your data as structured and un-redundant as possible. So I support pritaeas' advice to use lookup tables for coded values.
Regarding efficiency and performance, with properly set indexes you will not notice any difference if you use text strings or code numbers - at least not if your row count stays below a million or so. Therefore it might be easier to store each string explicitly, but with a foreign key reference pointing to a lookup table so that you cannot store 'uncontrolled' string values.

To your point, is what I'm doing with the Code table not essentially creating a lookup table for coded values? :?:

[smantscheff]

Your code table seem to me like a unstructured mass of text strings with codes attached. Each of the lookup tables refers to only one field in one table and makes sure that only certain values may be entered. You could have a similar effect by enum fields which list all allowed content strings, but they are not so easy to maintain as lookup tables.

[jrotunda85]

Your code table seem to me like a unstructured mass of text strings with codes attached. Each of the lookup tables refers to only one field in one table and makes sure that only certain values may be entered. You could have a similar effect by enum fields which list all allowed content strings, but they are not so easy to maintain as lookup tables.

It's set up currently with 3 fields:

Type Code, Code, Description

So for US States lets say, they all are a Type Code of 3 (this is how I pull them from the queries WHERE Type_CD = 3), Have Individual Codes that range from 30001 to 30050, and have the respective descriptions. So theoretically, they could be broken into smaller subsections. So would you suggest splitting each "Type Code" into a separate table?

[pritaeas]

Am sure smantscheff would agree: yes, splitting up is the right thing to do.

[jrotunda85]

Alright, so per your recommendations, I broke the code table up into the following sections:

tbl_BinderFiller
tbl_Length
tbl_Manufacturers
tbl_Ring Gauge
tbl_Shape
tbl_States
tbl_Wrapper

The only one I don't need for this particular page is tbl_States. So going back to the original question, what would be the best way to have these tables called and where the code (stored in the database) equals one then it prints the description found in these tables? By the way, these tables have two columns: Code, Description.

[pritaeas]

select * from cigar c
left join tbl_Length l on c.length_column = l.code

[smantscheff]

As for names, I wouldn't call a table tbl_... From the syntactical context it is quite clear where a table or view and where a column is being referred to. And a database is not only a means of storing data, but also a means of communication - between you and yourself in half a year when you have to modify your code.
Make sure that your cigar table has foreign keys into the lookup tables.
Add further LEFT JOINs to pritaeas' sample query to integrate the other cigar attributes into your query.

[jrotunda85]

Thank you both so much for your help and I apologize for this stupid question (I'm still getting the hang of mySQL and PHP) but this would be the command to create the foreign keys correct?

ALTER TABLE Cigar
ADD FOREIGN KEY (Wrapper) REFERENCES tbl_Wrapper(Code)

Also, thanks for the tip on the tbl part ;)

[smantscheff]

Yes, that's the way to do it.
You could enhance the statement with ON UPDATE and ON DELETE clauses, but with your standard plain vanilla version you are on the safe side so that you cannot delete entries from the lookup tables which are still in use.

[mschroeder]

Foreign keys are only relevant when the database tables are InnoDB. MyISAM does not support foreign key relationships in any way.

If you are using InnoDB you absolutely should be using the FK constraints for ON UPDATE and ON DELETE. These allow you to enforce your business rules on your data structure without having to implement the checks in your code.

e.g.
If you were to delete a cigar record, any table that contained additional information and referenced the cigar table primary key you could have those tables setup to cascade the delete, and remove the record in any linked table.

For modeling your database I highly suggest checking out MySQL Workbench (http://wb.mysql.com/) you can design the entire database in a graphical manner including the foreign key relationships etc and you can than export the sql for creating the database.

Also I would suggest adopting a solid set of naming conventions for your database. It will really make your life easier in the long run when everything follows the same conventions. Personally I use a set of conventions that are a slightly modified version of http://weblogs.asp.net/jamauss/pages/DatabaseNamingConventions.aspx

[jrotunda85]

OK, so I got everything set up however the code is not working. I did have to convert the engine on all the tables to InnoDB so that shouldn't be the issue. Here's the code:

Setting the Array

//Set Array
//$SQL = "SELECT * from Cigar WHERE Cigar_ID = $CigarID";
$SQL = "SELECT * FROM Cigar a WHERE Cigar_ID = $CigarID
LEFT JOIN cd_BinderFiller b on a.Binder = b.code
LEFT JOIN cd_BinderFiller c on a.Filler = c.code
LEFT JOIN cd_Length d on a.Length = d.code
LEFT JOIN cd_RingGauge e on a.Ring_Gauge = e.code
LEFT JOIN cd_Wrapper f on a.Wrapper = f.code";
$Result = mysql_query($SQL);
$Array = mysql_fetch_array($Result);

Called Code

Cigar Binder = <?php echo $Array[5] ?><br>
Cigar Filler = <?php echo $Array[6] ?><br>
Cigar Length = <?php echo $Array[7] ?><br>
Cigar Ring Gauge = <?php echo $Array[8] ?><br>
Number of Reviews = <?php echo $Array[9] ?><br>

I'm imagining it has something to do with the array but I'm not sure where else to put this code? :-/

[jrotunda85]

OK, I realized that the WHERE clause needs to go at the end and it's displaying now; however, it's not displaying the description from the lookup tables, just the code? Here's the revised code:

$SQL = "SELECT * FROM Cigar a
LEFT JOIN cd_BinderFiller b on a.Binder = b.code
LEFT JOIN cd_BinderFiller c on a.Filler = c.code
LEFT JOIN cd_Length d on a.Length = d.code
LEFT JOIN cd_RingGauge e on a.Ring_Gauge = e.code
LEFT JOIN cd_Wrapper f on a.Wrapper = f.code
WHERE Cigar_ID = $CigarID";

[jrotunda85]

Got it working! Apparently there was something wrong with the InnoDB conversion. Recreated the table and it's working flawlessly now. Thanks again everyone for your help! :)

[smantscheff]

I strongly object to mschroeder's view about ON DELETE/UPDATE clauses and using mysql workbench.
For professionals this might be the way to go. But I assume most forum participants as non- or parttime professionals who will do data editing with tools like navicat, heidi, command line mysql and the like, and not with systems which completely map business rules to database procedures. And on this level you will find it quite helpful if MySQL tells you that you cannot delete a record because of foreign key constraints. Then you see that your thoughts and logic have been misguided.
As for the GUI database design, I strongly recommend the command line over any GUI. Command line DDL and DML in the form of scripts is repeatable, updateable and - once you have mastered it - fast to code, much faster than pointing and clicking on GUI elements. And you have a much clearer impression of what you are doing.

[mschroeder]

If you do not use referential constraints with InnoDB than MySQL with NOT tell you it can't delete a record because of a foreign key constraint. Your checks for consistency in data will need to be performed in your code which opens yourself up for lots of other issues and additional checks that would not need to be performed with an understanding of how the constraints ON UPDATE and ON DELETE work.

Up until MySQL 5.5 MyISAM was the default table engine. This engine has no support for transactions or foreign keys even if you can use the syntax. Since MySQL 5.5 InnoDB is now the standard for table type, which it should be. Transactions and Foreign Keys are invaluable tools in database design especially when you get into more complex data structures.

The tool used to edit data is irrelevant in relation to foreign key constraints, navicat, heidi and even CLI are just accessors for the data and it's rules.

Learning SQL from the command line is invaluable and is the absolute go to for all of the reasons smantscheff pointed out. However, when working with teams of people and/or people who do not have SQL knowledge visual diagrams of your data structure and the relationships that exist within the data are key. Workbench happens to be the best tool I've see for this purpose. Workbench does not replace the understanding of sql, how to read and write it, it's datatypes, structure, optimizations etc. It is simply another tool in the toolbox.

[smantscheff]

I'm not arguing against referential constraints - your absolutely right in advocating them, and each novice should learn how to use them. I'm arguing against ON UPDATE/DELETE clauses because they will make happen things automagically which you better keep in mind and view when developing an application.