How to properly use tables in db?

[jay64]

I don't really know how to explain this easily. I guess I will layout the project I am trying to do, and the problem I am having with it, and maybe someone could explain it better to me.
I am trying to build an online food journal for a fitness/nutrition company. I have a form page made out that will allow their clients to input their eating habits for the day.
http://befit4riding.com/food_journal.html
I was thinking that I would have a db for each client, and each day would be a new table. I need to be able to store each day of data and keep it accecible for a later time. I also need to email the data to the company each time new data is submitted. Turns out, the server I am working on only allows 1 db. So how do I organize the tables so that they are all under the same client, but I can have multiple clients? Is there some kind of 'sub-table' (db/table/sub-table)? If I am not explaining myself adequately, please ask some specifics and I will be happy to try again.

notes on the page. I didn't have it automatically input the date, because sometimes people don't update everyday, so they need to be able to come in and put in multiple days at once, but have the correct date for each one. Eventually I might put in a calendar function, but I would have to learn that one and trying to just get this going first.

Thanks.

[jay64]

oops. I just read the announcement that daniweb doesn't want us linking to our sites because of spam reasons. I in no way was trying to promote the site, I just linked it to give a reference for what I was trying to do. If this is a violation, could one of the mods please remove the link from my post? I can't seem to find an edit post feature here.

[digital-ether]

I don't really know how to explain this easily. I guess I will layout the project I am trying to do, and the problem I am having with it, and maybe someone could explain it better to me.
I am trying to build an online food journal for a fitness/nutrition company. I have a form page made out that will allow their clients to input their eating habits for the day.
http://befit4riding.com/food_journal.html
I was thinking that I would have a db for each client, and each day would be a new table. I need to be able to store each day of data and keep it accecible for a later time. I also need to email the data to the company each time new data is submitted. Turns out, the server I am working on only allows 1 db. So how do I organize the tables so that they are all under the same client, but I can have multiple clients? Is there some kind of 'sub-table' (db/table/sub-table)? If I am not explaining myself adequately, please ask some specifics and I will be happy to try again.

notes on the page. I didn't have it automatically input the date, because sometimes people don't update everyday, so they need to be able to come in and put in multiple days at once, but have the correct date for each one. Eventually I might put in a calendar function, but I would have to learn that one and trying to just get this going first.

Thanks.

Unless you have two separate applications, you don't need two databases. You can even have different applications share 1 db by prefixing tables. eg: app1_table, app2_table

I think what you want to do is create relationships between your tables. (no db's don't have sub tables). Since your working with a relational database, you can create relationships between tables that simulates "sub-tables".

You don't need a db for every client, or table for every client. This is very inefficient.

What you want is a single table for your clients. Each client should have a unique index.
Then you want another table storing information about clients. Each info has a unique index, and another index with a value that corresponds to the index in the client table. This lets you know that this data is for that client.
If you need different data for different days stored, just create a column where you save a timestamp in your client data table.

It would be easier to understand if you have a good book on relational databases to guide you.

[jay64]

digital-ether, thank you very much for breaking it down like that for me. I figured it was something like that, but it wasn't showing any of that on the sites I was studying and I didn't know the terminology to search for it. Thanks. I'll look into the relational databases and unique indexes.

[Dani]

> I in no way was trying to promote the site, I just linked it to give a reference for what I was trying to do. If this is a violation, could one of the mods please remove the link from my post?

I cannot speak for the other mods, but I am more lax about this rule in the web development forums. What really pisses me off is when people post in the Internet marketing forums questions like, "Please give me suggestions on how I can promote my website www.mysite.com". It's also very hard to have candid discussions related to Internet marketing with the constant fear that everyone knows who you are and your competitors are there to see what you're doing and how you're doing it ... a general rule of not mentioning your site allows much more unbiased conversations because people are free to share their experiences about what worked and what didn't with regards to making money, advertising, etc,.

[jay64]

Ok, after doing some reading, I think I got an idea of how to structure the table, client_logs. I can have a row for the client, with a specific date, and then all the fields from the page for that date. Then when I want to pull up a specific day of log entry, I can query the db with SELECT * FROM client_logs WHERE client='value', date='value' That is what I got from my research on this, but it looks like digital-ether, has a slightly different method, which sounds like it might save time in the long run. Any suggestions/critiques/ways to do it better on this approach?

[digital-ether]

Ok, after doing some reading, I think I got an idea of how to structure the table, client_logs. I can have a row for the client, with a specific date, and then all the fields from the page for that date. Then when I want to pull up a specific day of log entry, I can query the db with SELECT * FROM client_logs WHERE client='value', date='value' That is what I got from my research on this, but it looks like digital-ether, has a slightly different method, which sounds like it might save time in the long run. Any suggestions/critiques/ways to do it better on this approach?

Thats pretty much the same as what I mentioned.
Do you have a separate table for clients?

So you're query would be like: SELECT * FROM client_logs WHERE client={clientid}, date='{timestamp}' LIMIT 1 where the {clientid} is the index of the client's row in the clients table and {timestamp} is any date/time format.

Note the LIMIT 1. It's good practice to let the DB know that you only want a single entry. The reason is that the DB stops iterating through its index as soon as it reaches the first valid row. Otherwise, it goes through all rows. If you have something like 100 000 rows, this makes a huge difference.

[jay64]

Thats pretty much the same as what I mentioned.
Do you have a separate table for clients?

So you're query would be like: SELECT * FROM client_logs WHERE client={clientid}, date='{timestamp}' LIMIT 1 where the {clientid} is the index of the client's row in the clients table and {timestamp} is any date/time format.

Note the LIMIT 1. It's good practice to let the DB know that you only want a single entry. The reason is that the DB stops iterating through its index as soon as it reaches the first valid row. Otherwise, it goes through all rows. If you have something like 100 000 rows, this makes a huge difference.

Well, actually, I don't have any tables made yet, still trying to understand it before I start making things. I don't want to do a lot of work and then have to go back and change it all around. I am having a hard time understanding why I would use two tables. I am trying to make it automated, that the client can go in and choose their own username and then start logging in to their food journal. If I have them automatically added to the first table, clients, then how does the second table know which cliented they were assigned?

Thanks for the note on the LIMIT 1.

[martin5211]

I recommend every-time as possible the guideline use mentioned by digital-ether. I will try to make a more deep review.

Create one table for clients, first field called id_(table name), type Int, as primary key and with auto-increment extra feature. Then, when you add a record, a new auto-incremented unique id number will be generated for each client. That's the normal usage for tables.

You can translate the client id to another tables to maintain the relationships between tables (e.g. for posts table, I suggest to create the first field id_post, the second as id_client and then the additional ones - title, body, etc.).

When you add a new client the question is how to insert the resultant client id in another table? Well, you could do a lookup by client name to get the client id before, using a input field or a dropdown list with client names and id values, or use

SELECT id FROM clients ORDER BY id_client DESC LIMIT 1

same as mentioned by digital-ether again, in this case to get the latest inserted id from clients. When you make a insertion in another table do not forget to insert the client id.

You could make a lookup by logon name, e.g.

SELECT id FROM clients WHERE username = $logon_name

To make a single query through several tables with a same field in common (e.g. client id) you could perform a query like this:

SELECT *
FROM clients
INNER JOIN posts ON clients.id_client = posts.id_client
WHERE clients.name LIKE 'john smith'

That statement will display results from two tables like it was a single table.
Or you could do simple requests into each table and merge the resultants arrays in one.

[jay64]

Digital-ether and martin5211, thanks for being patient and spelling it out for me. I think I'm starting to get a hang of it. Maybe I should start trying to make some tables and play with them. For the client table, do I only have the client names and unique ids? Or could I have other info in there as well, such as username?

[martin5211]

For clients I recommend to start with id, fname (first name), lname, username, email

For journal, id, id_client, title, body, date

You could use text type for any field except id (I suggest int, 11 chars length for this)

[jay64]

Thanks. I have found this code below for creating a table. I edited it to incorporate what was mentioned here previously. At the end, it has PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id), what exactly is this for? There was an short explanation, but it didn't make any sense to me. Also, there is no space after the ',' in the code. Is that correct? I think I would have a habit to put in a space if I wasn't just copying and pasting. Would that cause a problem?

$query="CREATE TABLE clients (id int(11) NOT NULL auto_increment,fname varchar(15) NOT NULL,lname varchar(15) NOT NULL,username varchar(20),email varchar(20),PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query);

[martin5211]

UNIQUE and KEY id_2 isn't necessary, let alone primary key (id), indexes are meant to use a basic field for lookups, you could use more fields like fname and lname to perform special full-text lookups using MATCH().

And yes, you could avoid spaces or make carriage returns between commas to make the code more readable.

[jay64]

Would this be the correct way to make the second table?

$query="CREATE TABLE food_journal (id int(11) NOT NULL auto_increment, id_client(11) NOT NULL, timestamp varchar(8) NOT NULL,water varchar(8) NOT NULL, breakfast varchar(8) NOT NULL, breakfast_calories int(8) NOT NULL, breakfast_time varchar(8) NOT NULL, snack_1 varchar(8) NOT NULL, snack_1_calories varchar(8) NOT NULL, snack_1_time varchar(8) NOT NULL, lunch varchar(8) NOT NULL, lunch_calories varchar(8) NOT NULL, lunch _time varchar(8) NOT NULL, snack_2 varchar(8) NOT NULL, snack_2_calories varchar(8) NOT NULL, snack_2_time varchar(8) NOT NULL, dinner varchar(8) NOT NULL, dinner_calories varchar(8) NOT NULL, dinner_time varchar(8) NOT NULL, snack_3 varchar(8) NOT NULL, snack_3_calories varchar(8) NOT NULL, snack_3_time varchar(8) NOT NULL";
mysql_query($query);

[martin5211]

There is some little mistakes, like missing type on id_client field, a UNIX timestamp normally takes ten positions and lunch_time have one space inside (table names can't have any space). I recommend make the first field (id) as primary key usually.
The resultant SQL would be:

CREATE TABLE food_journal(
id INT( 11 ) NOT NULL AUTO_INCREMENT ,
id_client INT( 11 ) NOT NULL ,
TIMESTAMP INT( 11 ) NOT NULL ,
water VARCHAR( 8 ) NOT NULL ,
breakfast VARCHAR( 8 ) NOT NULL ,
breakfast_calories INT( 8 ) NOT NULL ,
breakfast_time VARCHAR( 8 ) NOT NULL ,
snack_1 VARCHAR( 8 ) NOT NULL ,
snack_1_calories VARCHAR( 8 ) NOT NULL ,
snack_1_time VARCHAR( 8 ) NOT NULL ,
lunch VARCHAR( 8 ) NOT NULL ,
lunch_calories VARCHAR( 8 ) NOT NULL ,
lunch_time VARCHAR( 8 ) NOT NULL ,
snack_2 VARCHAR( 8 ) NOT NULL ,
snack_2_calories VARCHAR( 8 ) NOT NULL ,
snack_2_time VARCHAR( 8 ) NOT NULL ,
dinner VARCHAR( 8 ) NOT NULL ,
dinner_calories VARCHAR( 8 ) NOT NULL ,
dinner_time VARCHAR( 8 ) NOT NULL ,
snack_3 VARCHAR( 8 ) NOT NULL ,
snack_3_calories VARCHAR( 8 ) NOT NULL ,
snack_3_time VARCHAR( 8 ) NOT NULL ,
PRIMARY KEY ( id )
)

[jay64]

I recommend make the first field (id) as primary key usually.

I thought you stated that the primary key (id) wasn't necessary??

UNIQUE and KEY id_2 isn't necessary, let alone primary key (id), indexes are meant to use a basic field for lookups, you could use more fields like fname and lname to perform special full-text lookups using MATCH().