My First Database + some

[metabeat]

Hi there... I bought Kev's book, and it's been real helpful so far... picking up on SQL and PHP quite fast for not having programmed b4.

Anyhow, I need to design a database to track Daily Specials at a friends restaurant. There will be between 6 and 9 specials a day, and each special needs a description in english and spanish plus a price. In addition I want to upload the specials to their website every day.

I was thinking of making one table and having the three fields per special for a maximum of nine specials, which would give me 27 fields per row... plus one field for date (and maybe a unique ID, or could i use the date for this?).

Is this the right way to go about things?
Or is there a better solution? I also need to print the specials for the menus every day, I heard you can do some pretty kewl things with PDFs and PHP... or should I just stick to HTML?

Thx!!

[samsm]

Sounds good to me... except I'd change "maybe a unique ID" to "definately a unique ID".

Most menus I see (and especially their inserts) could be made with HTML but if you have quality graphics or something you'd want to use... give it a shot.

You have to add a special module to create pdfs... make sure your host has it or you can install it if you are going to try.
http://www.php.net/manual/en/ref.pdf.php

[Technosailor]

Will the specials be the same from week to week? Meaning, is the Soup du Jour going to be Cream of Crab every Wednesday and Thai Peanut every Friday? If you could count on the same specials for each day of the week, you might consider making the table a listing of all the specials with an id, name, english, spanish, price AND an identifying field for which day the order is related to.

OR, even better, have two tables. One itemizing the specials, (id, name, english, spanish, and a price) and then have another table of the days and reference the id of the special. That way you could have Cream of Crab soup on Wednesday one week and Thursday the next.

Does that make sense?

Sketch

[metabeat]

thx so much for the feedback there, man these forums are really alive!

well, the cooks get pretty excited so they have different stuff every day and there is no repeating schedule to the dishes. They just want to be able to go back to a date and see what they made so they get inspired.

I also wanted to ask if I should impose any sort of limit on the amount of characters the dish descriptions will have.

The other issue I have is that the number of specials can change from 6 to 9, but i have to print the specials sheet full page every time... any ideas on how to do this? Is there a cleverer way than using a template for each possible number of dishes?

Thx again for the help!

[Technosailor]

well, with my second example, you can do a SELECT query and have a WHERE clause that singles out the particular day. Then put it through a while loop and make an array out of it. So if you have 7 specials on Friday then 7 Specials will be pulled from the db. If you have 9, 9 will be pulled. Maybe one day is a little sparse and you only have 4 specials. That day's query will result in 4 resultsets.

HTH.

Sketch

[metabeat]

Thx for your help Sketch, I'm starting to get a clearer picture now... Just a few things I still wanna clear up;

how would the staff then enter the specials? Would they have to enter the specials and then assign them to a date? The ideal situation is for them to just enter the day's specials on a page and then they get stored.

What I'm debating here is wether i need more than one table, because what they need to do with this information is quite limited and the specials are almost always different. And I don't want to overcomplicate things for the staff.

Also my problem with the different numbers of specials is actually printing them so that they span one page, that's why i mentioned templates...

[Technosailor]

Having multiple tables is not bad and can actually simplify things for the staff. Don't be afraid to have multiple tables. As for entering, you'd have to iron the wrinkles out, but you could do a form that would query the db for existing specials and they can assign the date they want it to be aspecial on...insert that into the db, or enter a new special altogether.

That's vague but that's how to do it.

Sketch

[samsm]

I don't know where you are in "Building a Database Driven Website with PHP & MySQL" but the chapter about relational database design is good. Those techniques are well worth the time spent learning them.

Multiple tables won't make life any more difficult for the people editting the info... it'll just make your project a little more complicated and lot more flexible.

That said, if the specials are different every day, I see no reason why what you want couldn't be done with one table, the five columns you mentioned before. Just a matter of how far you want to take it.

[metabeat]

Ok I just came up with another issue. Just to keep u guys updated, I am now planning on creating three tables, one for specials, one for categories, and a lookup table to link both of these together.

I just found out today that i have to track specials for the night menu aswell, which is different.

Should I create a new night specials table and another lookup table, or should i store the night menu items in the original specials table? If doing it this way i guess i'd have to specify wether a special was a night special or a day special. Which is the best solution though?

I am also planning on having a menu_order column for each special so that users can reorganise the order special print in after having entered them. How does that sound?

Thanks again!

[Technosailor]

I can't imagine why you would need a category table. This is my understandiung of what you have.

specials
========
sID int() autonumber,
name varchar(15)
category varchar(15)
descrip text

If you have to sort by all Galician food, you could just perform a "SELECT * WHERE category = 'galician'" query. Unless there's some special data in the category table that I am unaware of.

So if you go without the category table, you don't need the lookup either.

But moot point. The question at hand. Different specials for different times of the day.

I am assuming that you might have the same special for lunch on the February 3 as you had last September 8th for dinner. All an assumption, of course. Perhaps you have a list of dinner specials and a list of lunch specials. If that's the case this might change a bit. But assuming you can pull specials for lunch and dinner from the same place, I would create 2 temporary tables: currentLSpecials and currentDspecials.

Create a web form to let the chefs input new dishes into the specials table. When it comes to creating that days specials, I'd build a form interface that allows the chefs to choose spcials via checkbox that are assigned values from the db, (or go to the input new special page). This would be a form, and pressing submit would cause a query like this:

INSERT INTO currentLSpecials 
SELECT specials.name, specials.category, specials.descrip
FROM specials
WHERE sID = '$checkbox_value_from_form'
SET 
currentLSpecials.menu_order = '$lunch_dinner_radio_on_each_selection_on_form',
currentLSpecials.name       = specials.name,
currentLSpecials.category   = specials.category,
currentLSpecials.descrip    = specials.descrip; 

Do likewise for currentDSpecials. I think this is how I'd do it, but it's untested.

Sketch

[metabeat]

ok got that, but if that is the case how do the specials actually get tracked through time... i mean i will have a table with all the specials, and what I have understood as temporary specials tables, but where do i store the information about what specials have been on each day for lunch and dinner, and in what order they were on the specials menu? Can I use arrays for this table and what type of table shoudld it be?

I'm still on the database design stage for this project, I want to get it right before i start coding...

Also, there's the problem of letting the chef's input the daily specials in one step, without having to later search for the specials they entered and assign them to a day...

i want to create an interface where they can enter the days specials and see what they have entered so far for the day, and then edit specific specials (or rather add new ones or they would delete the old ones which had already been used) and also reorder the specials on the menu. They should be able to switch from editing the day menu to editing the night menu.

Thx!

[Technosailor]

I've got to think on that. One way is to create yet another table of date that is updated automatically with the specials. But something tells me that's not the way to do it. Let me think on this and hopefully we can get some other input on this as well.

Sketch

[metabeat]

ok great and thanks again for all the help!

[metabeat]

Ok here are the three databases I am planning on using:

mysql> describe specials;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| ID      | int(11)      |      | PRI | NULL    | auto_increment |
| EN_desc | varchar(255) | YES  |     | NULL    |                |
| ES_desc | varchar(255) | YES  |     | NULL    |                |
| price   | decimal(4,2) | YES  |     | NULL    |                |
| CID     | tinyint(4)   |      |     | 0       |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.12 sec)

mysql> describe days;
+-----------+------------+------+-----+------------+-------+
| Field     | Type       | Null | Key | Default    | Extra |
+-----------+------------+------+-----+------------+-------+
| specialID | int(11)    |      |     | 0          |       |
| date      | date       |      |     | 0000-00-00 |       |
| courseID  | tinyint(4) |      |     | 0          |       |
+-----------+------------+------+-----+------------+-------+
3 rows in set (0.00 sec)

mysql> describe categories;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| ID    | int(11)      |      | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.42 sec)

I think I can do all that I need with them. The basic function of the days table will be to list what specials will be in a day and wether they're lunch or dinner specials. I can then pull this info to create a daily specials page for either dinner or lunch. I have a question about the days table however, I haven't assigned it a Primary Key. Is one necessary and if so how what should it be?

[Aes]

Ragarding the chefs being able to see what they've entered for a given day, are you talking about at various times throughout the day or simply before they finally insert the data into the database?

If the case is the latter, I would simply add a "preview" button that cycles through and when it is pressed, what they've chosen so far is displayed for them on the page before actually being entered into the database.

If you're wanting various times throughout the day, you'll simply need to sort it chronologically and have PHP dyanmically gather the current time, compare it with the entries in the database, and pull out the corresponding data.

For the PRIMARY KEY, I would recommend assigning it to one of the "specialID" since that is to what you're referring the day.

All this is conceptual -- what else do you need? If you need something more specific, such as query assistance, be really specific and we can help you out. Good luck.

-Colin

[metabeat]

For the chefs, i simply want them to be able to see what's on the specials menu so far as they're entering the information. I thought of them entering a special one by one, or being able to assign specials that are already in the database to the day's list. This would either be done in the morning or the night before.

As for the primary key, the problem I see with assigning it only to the specialID column is that this column is going to have many values repeated, since some specials will be assigned to more than one date. Similarly, each date will have more than one special and will appear many times in the table (once for each special).

I learnt that lookup tables for many-to-many relationships should have multi-column Primary Keys, i am wondering how that applies here...

Thx again for all the help!