Foreign Keys

[1cookie]

hi

 

First a quick example to explain my thoughts. Let's say I have two tables: customer and order. Now a customer can make many orders and an order is associated with one customer - one-to-many relationship. In MySQL I'm used to seeing relations like the following:

 

customer = {cust_id, f_name, s_name, ...., dob,...} & order = {ord_id, cust_id, ord_content, ord_total,..., ord_date,...} say. Where the cust_id is the link (foreign key) between the two tables. My point here is that the two fields are named the same and would be of the same data type. This is how I'm used to seeing one to many relationships in MySQL.

 

Is this purely a matter of preference?

 

I mean, from Larry's book, I have the following two tables:

 

CREATE TABLE IF NOT EXISTS `categories` (
`id` smallint not null auto_increment,
`category` varchar(30) not null,
PRIMARY KEY (`id`),
UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

CREATE TABLE IF NOT EXISTS `pages` (
`id` mediumint unsigned not null auto_increment,
`category_id` smallint unsigned not null,
`title` varchar(100) not null,
`description` tinytext not null,
`content` longtext not null,
`date_created` timestamp not null default current_timestamp,
PRIMARY KEY (`id`),
KEY `category_id` (`category_id`),
KEY `creation_date` (`date_created`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

where a category can have many pages and one page belongs to one category - 'one-to-many'. Only, from the book I don't see matching field names (foreign key)? I mean where's the foreign key in this relationship? Is it between id in categories and category_id in pages? Bearing in mind they are of the same data type.

 

Can someone clarify.

[Jonathon]

Quickly looking at this you're right. I would guess Larry does it like that as there are times you will have various foreign keys within one table and it's just a clearer way to to workout what the key represents.

[1cookie]

Quickly looking at this you're right. I would guess Larry does it like that as there are times you will have various foreign keys within one table and it's just a clearer way to to workout what the key represents.

 

So just to reinforce that:

 

Naming conventions are a matter of preference - but both fields MUST be of the same data type?

[Jonathon]

Most foreign keys are usually numeric Id fields from other tables, so they would normally be integers.

[HartleySan]

Yes, naming is a matter of convention, but the fields must be of the same type.

 

From the MySQL home page:

 

Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

(Source: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html)

[1cookie]

Yes, naming is a matter of convention, but the fields must be of the same type.

 

thanks.

[Larry]

To be frank, I go back and forth with how I name the primary key, and using frameworks has influenced that. So sometimes it'd be "id" as the PK and "category_id" as the FK and other times I'll end up doing "category_id" for both.

[Antonio Conte]

When writing database structures, the normal way to do it is like this:

 

table (primary_key, foreign key*, other_rows)

 

It just makes it easier to read. I also like to name foreign keys with tablename_id. Adding fk after the name id_fk is also used to see that it's a foreign key.

[HartleySan]

Larry, you mentioned using frameworks. What frameworks do you like, and why do you like them?

[Larry]

I like the Yii framework quite a bit. For starters it requires PHP 5 and uses jQuery natively. Then I like how it auto-generates a lot of code and folders for you. From there, it just kind of works and makes sense to me.

 

I've used the Zend Framework some, but didn't like using it as the basis of a site. I use pieces of it as needed, which is nice.

 

That's about it for PHP frameworks that I've actively used.

[Antonio Conte]

You should take a look at the CodeIgniter framework as well, Larry.

 

I've had trouble just installing YII, so I've haven't got around to try it.

[Larry]

I've heard good things about CodeIgniter, among others, but Yii seems to be fitting my needs for now.

[Matt]

I like the Yii framework quite a bit. For starters it requires PHP 5 and uses jQuery natively. Then I like how it auto-generates a lot of code and folders for you. From there, it just kind of works and makes sense to me.

 

I've used the Zend Framework some, but didn't like using it as the basis of a site. I use pieces of it as needed, which is nice.

 

Larry,

 

Sorry to sound abrupt, but I really have to ask; Why in the hell would I want to use someone else's glorified api (which is all a framework is in my opinion) when I can do things myself using your books? I mean, what is the point of learning anything about php if I can just bloat my server disk space with someone else's code (half of which I`ll probably never need) and just call functions? Can you give any specific examples of when a framework might be better to use? Can they replace the brilliant code you have given us in the E-Commerce book?

 

When I hear all this ranting and raving about how wonderful frameworks are, I really have to ask myself why I am working hard studying the more difficult aspects of php! Maybe I should just give up and install Yii!

[Larry]

Hey Matt,

 

I just want to say up front that you ask excellent, worthwhile questions, but I find that you do so a bit too bombastically (I'm also thinking of your other post regarding hosting). These two posts seem that much more excessive to me as it seems (to me) that you are missing the point, and emphatically so. Just look at some of the terms and phrases you use: "Why in the hell", "someone else's glorified api", "what is the point", "ranting and raving". Clearly there's a less aggressive way you could raise the same issues. So perhaps you could tone down your rhetoric a bit when you post questions like these? I appreciate the questions and alternative viewpoints, but a little less "Is everyone else an idiot?" would be for the best.

 

The first thing to understand is that it's not an either/or situation: learn and use PHP and MySQL from scratch OR use a framework. Realistically, you can't use a framework without having a solid understanding of the underlying technologies. Second, understand that I'm a "gray area" kind of person: trying to see the arguments for both sides, trying to avoid thinking of things as always whatever or absolutely whatever. That being said...

 

The clear benefit of using a framework is speed of development. In very few cases will using a framework NOT be faster than coding from scratch. Once you've learned the framework, that is. A framework-based site may or may not be more secure, easier to maintain, and less buggy than a scratch-based one, depending upon the site, the framework, and the programmer. Finally, a framework-based site is likely to have more features than a scratch one, unless you put in the extra effort. For example, Yii uses jQuery by default and enables Ajax and some other stuff out of the box, so a Yii-based site that I write will likely have an extra bell and whistle or two than one I create from scratch. And, again, have those extra features in far less of my time.

 

The clear downside of using a framework is extra overhead, in terms of both extra files and poorer performance. That may sound terrible but the counterpoint is that it's much, much easier to make a poorly performing Web site perform better than it is to get hours back in your life that you spent developing a project. Also, frameworks have built in caching to rectify this problem whereas most developers never get around to adding caching to the scratch sites they develop.

 

So are frameworks a better way to go? Sometimes yes and sometimes no. Personally, I've worked on maybe 8 or 9 sites in the past two years and used a framework (specifically Yii) on maybe 5 of them. For those projects, the benefits I get from using the framework outweigh the negatives. For the other sites, the benefits of using a framework didn't merit going that route. But the most important thing to know is that frameworks are sometimes a better solution and I think the intelligent thing to do would be to pursue and try using a framework before you dismiss them outright.

[Matt]

Thanks for the detailed explanation Larry! That was exactly what I wanted to hear. With all the talk of frameworks on this forum, and elsewhere, I just wanted to get a good idea of when it's appropriate to use them or not. I guess I'm bringing in a little backgroud from Flash into this. There are literally hundreds of Actionscript 3 APIs available on the web. I have often used them for one thing or another because I don't want to reinvent the proverbial wheel. However, I am very particular about things, and what I find myself doing 9 times out of 10 is customizing the API to suit my needs! With that in mind, I was looking at frameworks as the "easy way out", and that I would still have to do massive customization to get them to do exactly what I want! Anyway, now I have a better appreciation for using frameworks, so I will look into them more.

 

Also, I apologize for the extreme tone in my posts! I guess it's my style when I don't understand why people are doing something a certain way. It's part serious and part show because I'm trying to elicit a good answer which supports the thing I'm arguing against (if you think that's bad, you should hear me talk about politics and U.S. foreign policy)! I just hope nobody took offence to it, and was perhaps entertained a little I will try and relax a bit before I post questions like this again.

[Larry]

Hello Matt,

 

I think frameworks could be considered "taking the easy way out". The fallacy is in thinking that taking the easy way out is a bad thing. We all take the easy way out with all sorts of things all the time. It's up to you, the programmer, to decide whether taking the easy way out is appropriate for a given project.

 

Now as for US foreign policy...