Is my table right? & advice about adding these extra fields

[Markbad311]

I have a table for events here is a link to a picture I grabbed from phpmyadmin (http://www.eriescene.com/table.jpg) .

What I need to do is add to the end of the table two fields.

1. 'highlights' and this is going to be like true or false. 0 or 1 like a checkbox
2. 'holiday' this will specify if it is a holiday event will be varchar 50chars

I plan to use this so I can use one table for news and events accross the whole site and just basically specify what gets picked out on what page with it own sql statement.

What I need to know is.
1. Is my table currently set up right? My Primary key is ID. and thats all thats special about it.
2. Should the holiday and highlight field be like: index on foriegn key?

I know nothing about these special attributes to each one of the fields so I am basically needing help understanding what will make my table better and faster. So I can use more powerful mysql statements.


MySql ver#:4.1.14-standard
PHP ver#:4.4.1

[NogDog]

Foreign keys are used for linking two tables together on a common column, so are not applicable at this time. For query efficiency, you may want to create indexes on any non-key, non-unique columns (which are automatically indexed) which are used in where clauses in your select queries.

[Markbad311]

I select most from the date area but it may be equal when I create these two other fields. these fields will help define just what page this stuff should go on.

so maybe make the date an index?
and the bar field a foriegn key. because it will be in used with another table I will create in the near future.

Am I kinda correct? please bear with my personal MySql skills. but how for example, would these work more effieceint in querys (please provide an example for both) then on the application level?

[Markbad311]

Woa follow up question isn't my true or false field for 'highlights' suppose to be boolean? and how do I use that is says to use it like (bool)??

[NogDog]

MySQL doesn't have a true boolean type. Type BOOLEAN (or BOOL) is actually just a synonym for TINYINT(1). A value of 0 is considered "false" and non-zero values are considered "true".

[Markbad311]

I have a current MySql Query Like this for my events page (http://www.eriescene.com) :


$sql = "SELECT date, bar, DATE_FORMAT(`updated`,'%M-%d-%Y') updated, details, map_url FROM $table_name WHERE `date` BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) and DATE_ADD(CURDATE(), INTERVAL 30 DAY) ORDER BY highlight DESC LIMIT $start, $show";


It works fine, yet I want to define my order so if it is a highlight it displays first then the rest is ordered by the name of the bar alphabeticaly

these don't provide the results I want.
ORDER BY highlight, bar
ORDER BY bar, highlight

If you click on the link above you will see Fat Tuesday is on top because it is a highlight event. but after the three fast tuesday listings I would like the rest to be ordered by "bar" (the Name of it that is stored in the bar field) Each heading is an example of the "bar" field