Creating table views

[geller]

I'm in the process of setting up a database driven 'restaurant menu' website where the restaurant owners are able to update their own menus and restaurant details etc.

I am using a single table per restaurant but want to create different views from that table, effectively make it appear like 4-5 individual tables. Is there a way within dataface to do this.

[njw]

There may be a way, but why are you using a single table when you really want 4 or 5? Surely it would be much easier with a normalised database with tables for: restaurant details, menus (by week perhaps?), dishes (then they can be easily added back in again), etc. Dataface would handle that structure easily. You could add the restaurant code on each record if you wanted to separate the restaurants.

Different views could be done with coding, but why paddle against the current?

Neil

[shannah]

I'm not sure that I understand completely what you are trying to do. A data-driven restaurant menu web site would be well suited to Dataface though.

Some specific questions whose answers may help me to understand better:

You say that you are using a single table per restaurant. Does this mean that for each restaurant you have a separate table? Or to you mean that you have one table and you are storing the data for all restaurants in this table?

Just off the top of my head, you'd probably want to have structure similar to the following:

Tables:
restaurants
menus
dishes

Table descriptions:
restaurant table holds information about each restaurant like name, address, contact info, etc..
menus table holds information about a particular menu (perhaps a wine menu, or a lunch menu, or a dinner menu, etc..).
dishes table holds information about a particular dish (name, description, prices, photo, etc...)

Relationships:

Each restaurant can have many menus. Each menu can have many dishes.

With Dataface you would set it up so that a particular restaurant owner would go directly to the record for his restaurant. There he would see info about the menus and dishes for that restaurant (using relationships).

Does this sound like what you want to accomplish?

-Steve

[geller]

I guess ideally that is the kind of structure that should be implemented but I have approached from a non DB orientated background. I was also under the impression that if a table were being updated it is locked while the update is taking place. If numerous people have the ability to access and update then in a normailised structure they will be trying to alter the same table/tables?

I am using 2 databases one which is accesible only by the webmaster with all the contact details for each place on it which is searchable. Then a second DB which will be accessible by the owners for them to update their homepage and menus.

I started with multiple tables but changed to 1 table per establishment for ease of implementation and manageability (200+ restaurants.... maybe!). Every aspect of the owners page is changeable by him apart from the layout & colour scheme. The owner table has only 27 fields and would require 5 views:-

1 for the restaurant homepage details
`KEY`,
`restaurant_name`,
`address_details`,
`opening`,
`paragraph1`,
`paragraph2`,
`image`,
`menu_table1`,
`menu_table2`,
`menu_table3`,
`menu_table4`,
`visa`,
`delta`,
`mastercard`,
`switch`,
`solo`,
`accessible`,
`baby_changing`,
`smoking`,

2
`board1_description`,
`board1_price`,
3
`board2_description`,
`board2_price`,
4
`board3_description`,
`board3_price`,
5
`board4_description`,
`board4_price`

I know that it is not the correct way to do it and a relational DB would be far superior because a user could then search for a specific meal for example and bring up the restaurants.. perhaps in the future....

I have tried other code generators phprunner for example and can achieve it with that but I think that the way dataface is constructed is ideally suited for scalability which is what I am looking for.

http://www.eatout-iom.co.uk/creek/ very basic at present but functions!

Cheers Graham

[njw]

Hi Graham

I still recommend that you use a relational structure. There is a recent post that tells you how to implement locking if you need to, but the locking is at record level, not table level. Many people can modify a table at the same time without problems, unless two people try to modify the same record at the same time. In your scenario, this is very unlikely.

The realtional design also makes it much easier to manage into the future as you add functionality.

I'm happy to help with the design if you wish.

Neil

[shannah]

quote:----------------------
I was also under the impression that if a table were being updated it is locked while the update is taking place. If numerous people have the ability to access and update then in a normailised structure they will be trying to alter the same table/tables?
----------------------------: end quote

I wouldn't be too concerned about this. MySQL is used to power a lot of very busy sites that are being updated constantly by multiple users. Unless you're talking about people saving hundreds of records per second this won't be an issue. With only 200 restaurants this certainly wouldn't be an issue. If you were running 200 thousand restaurants you would have to take some care in your design to handle the scale, but you can cross that bridge when you come to it.

quote:----------------------
I am using 2 databases one which is accesible only by the webmaster with all the contact details for each place on it which is searchable. Then a second DB which will be accessible by the owners for them to update their homepage and menus.
-----------------------------: end quote

Just a note. You are able to assign quite fine-grained permissions at even the record level using dataface. Even if everyone's information is stored in a single table, you can easily make it so that people can only update their own information - or that only the webmaster can update certain records or certain fields.

quote:-----------------------
I have tried other code generators phprunner for example and can achieve it with that but I think that the way dataface is constructed is ideally suited for scalability which is what I am looking for.
------------------------------: end quote

Just a note. Dataface is actually not a code generator. It is a framework that enables you to build applications without writing very much code. Code generators generate code for your application but the code tends to be difficult to manage in the long run, since you would have to make changes to the code (sometimes tens of thousands of lines of code) if you need to modify your app. Dataface, on the other hand, does the heavy lifting for you so that you can focus on the important parts of your application. It uses configuration files to set up the behavior of your application, but no code is generated. If you want to change the configuration, you just change the configuration file. It allows you to extend your applications using PHP, but all of the actual code in your app would be your own - not generated.

Subtle distinction, but worth noting i think.

Best regards

Steve

[geller]

Steve/Neil

Thanks for your comments and reassurances regarding relational databases and Dataface Both now appear ideally suited to my needs/requirements.

-Neil if you would be happy to suggest a structure for a database and it is not too much trouble for you, I would be grateful. Please remember I ain't no DBA as you will probably have guessed.. so if you could take it easy on me!

Cheers
Graham

[njw]

Steve's template above is a good place to start. Keep it relatively simple to begin with. I have assumed that you can use phpMyAdmin or similar to set up the MySQL tables.

Using your restaurant table as defined above, add a field for UserId. This will allow you to restrict update access to restaurant data

Also consider whether to have a separate field for "Nearest town" and possibly distance from that town centre. In time, this could get more complex by having a table of towns to allow you to select the towns from the table, and then display the restaurants associated with that town. I am building something similar at the moment that you can have a look at if you wish - nothing difficult to do as I am not much of a PHP programmer and my HTML is pretty basic also - www.archomai.co.uk/ACF (user: guest / password: limited). This is read only, but gives you the idea of what can be done.

For the Menus table:

MenuId (Primary key)
RestaurantId (used to tie menus to retaurants)
Menu Name
StartDate
EndDate

Dishes table:
DishId (Primary key)
RestaurantId (You may want this to be specific to the user rather than the restaurant, but this is the simpler route).
Description
Price
Special (flag whether a special or not - or text to describe when this is available)

MenuDishes table:

MenuDishesId (Primary Key)
MenuId
DishId


Set up the login script (see Steve's tutorial) and consider whether you want account data on the User table for your own purposes e.g. full name, address details, contract length. live flag, etc. This will allow you to restrict people to their own restaurants/menus/dishes through the permissions system - see Steve's notes - but you can add this code later once the rest is working.

Within Dataface, set up the restaurant and dishes tables in conf.ini as the only tables to display in the tables side menu for now.

Then set up relationships.ini files for as described in the "Getting Started with Dataface" tutorial:

Restaurant -> Menus
Menus -> Dishes using MenuDishes

You'll need to use the __sql__ option as you need to restrict the SELECT to the current restaurant only.

Set up formatting instructions using fields.ini files. For instance, I usually hide all the Primary key fields, set bigger text areas than the default, change some of the labels, etc. See the documentation on the fields.ini file.

Hopefully that will help you get started. In time, you may be able to have a single dataface application for edit and view, or you may find it easier to have separate applications.

[geller]

-Neil

I am a little lost, have created all tables but it is the relationships that are confusing me. My restaurant table(restinfo) doesn't appear to have any field in common with any other table? So I have created a field 'restaurantid' for that table.

Can you describe the relationships (sql joins etc) and which 'tables' directory the ini files should go.

(Restaurant -> Menus)

/tables/restinfo
[menu]
menu.restaurantid = "$restaurantid"

(Menus -> Dishes using MenuDishes? help)

I am afraid I am too new to dataface and sql relationships to grasp it just yet.
Cheers

[njw]

First, you don't need to define the relationships in SQL, just the tables. Dataface handles the rest. You seem to have set the restaurant - menus relationship correctly. This goes in tables/restinfo.

Because the menus - dishes relation could be a many to many relation, you need a table that connects them together. At its simplest this will have three fields:

MenuDishesId
MenuId
DishesId

MenuDishesId is just a unique primary key.

This table will not appear to a user, it is just used to connect the Menu and Dishes tables together.

In tables\menus, create a relationships.ini file. This needs to hold the more complex relationship definition:

[Dishes]
Dishes.DishesId = MenuDishes.DishesId
MenuDishes.MenuID = "$MenuID"

This will then allow you to add dishes to your menus, and see the list of the dishes within each menu.

It is probably a good idea to add some test data to your main tables (restaurants, menus, dishes) before trying Dataface as it should be easier to see what is going on.

Its worth persevering - it really is good.

Neil

[geller]

hi neil

Ok I have done that bit but I am still a bit puzzeled with the structure.

If an owner wants create separate starter, main menu and specials menu can this be acheived with this structure? How would they determine what description went into each?

Have a look

http://www.eatout-iom.co.uk/datafaceapi/

look a record 110 in restinfo

cheers

[shannah]

Hi Graham,

I took a look at your app and it looks like it's coming along. First thing I noticed though is that it is not picking up the dataface stylesheet. In your index.php file there is likely a line near the beginning that says:

df_init(__FILE__, 'http://eatout-iom.co.uk/dataface-build');

The url as the 2nd parameter of df_init() has to be a valid url to the dataface directory. The url you have supplied, however, is not valid url - it cannot find the directory. Your app will look much better when this gets sorted out.

I looked at record 110, and it appears as though you have found out how to add multiple menus. Correct me if I'm wrong.

Best regards

Steve

[geller]

Looks much better now. If I could just get the relationships thing right in my head.

I am looking at this from an end user perspective. If you refer to the user homepage http://www.eatout-iom.co.uk/creek/ then the user expects to edit 4 menus but this is not what he sees when logging into dataface. I am sure that this will confuse your 'average joe' it does me. (Am I missing something?)

I still cannot see the link between Menu and dishes. If I could click on starters and then edit just them directly then that would be logical to me.

Dataface is impressive though...

[shannah]

Hi Graham,

You're on the right track. Looks like the foundations are in place. You just need to start customizing things a bit to that the flow of control is more to your liking.

Your first issue mentioned is that people logging in don't see 4 menus. They only see the menus currently in the relationship (this could be none). Is it the case that every restaurant should have 4 menus: starters, main, etc...? Or do you want these just to be the defaults.

If you want the restaurant to start off with 4 menus, then you could use the afterInsert() trigger to automatically add the 4 menus when a new restaurant record is created.

Another thing that can do wonders for intuitivity is to override the rather bland "view" tab. You can do this with the block__view_tab_content() method in the delegate class.

for example

To override the view tab contents for the Restaurants table your delegate class would look like:

Code: Select all

function block__view_tab_content(){

    echo 'My new content!';

}



Try that example to get it working, but before going too far, I recommend you start incorporating Smarty templates for your output.

e.g.

Create a directory named 'templates' in your application folder. Add a file named 'Hello.html' inside this folder with the following contents.

Code: Select all

Hello world!!


Now change the view_tab_content() method to:

Code: Select all

function block__view_tab_content(){
    $context=array();
    df_display($context, 'Hello.html');
}


Now your view tab will say "Hello world!!".

Now pass some variables to your template:

Code: Select all

function block__view_tab_content(){
    $app =& Dataface_Application::getInstance();
    $record =& $app->getRecord(); // gets the current record
    $context = array('restaurant'=>&$record);
          // makes the restaurant $record available to the template
          // in the $menu variable
    df_display($context, 'Hello.html');
}



and you can use the menu from your Hello.html template by:

Code: Select all

{$restaurant->display('Restaurant_name')}

.. print more information about the restaurant....

Menus

{foreach from=$restaurant->getRelatedRecordObjects('Menus') item=menu}
   
• {$menu->display('Menu_name')}

{/foreach}

...

Hope this gets you started... The Dataface way is incremental development. You have a good frame for your app... now you incrementally add the little bits that will make it suit your needs better.

Best regards

Steve

[geller]

How would I extend the login script so that when user 'creek' record 110 in restinfo, logged in all he saw was the details for this record only (110)

Ideally this user should have a role of OWNER as the record can only be edited and not deleted or extra records inserted in this table.
Cheers