MySQL Relational Database

Dysan

Hi,

Using PHP & MySQL, how does one create a relational database?

laserlight

Using PHP & MySQL, how does one create a relational database?

I would not use PHP. Rather, I would login to the server and create the MySQL database by issuing a CREATE DATABASE command.

Dysan

How do I convert the following database into a relational database? The database contains peoples favorite food and drinks.

if (mysql_query("CREATE DATABASE db",$con))
{
  echo "Database Created!";
}
else
{
  echo mysql_error();
}

mysql_select_db("db", $con);
$sql = "CREATE TABLE persons 
(
  name text,
)";

mysql_query($sql,$con);

$sql = "CREATE TABLE items 
(
  favouritedrink text,
  favouritefood text,  

)";

mysql_query($sql,$con);
mysql_close($con);

laserlight

How do I convert the following database into a relational database?

What do you mean? What followed was PHP code, not a database.

If you want help in designing a relational database schema, present the entities, their attributes, and their relationships, that you are trying to model.

Dysan

Basically I want to create a relational database, using PHP code. How do I adapt the code posted in my previous post to reflect this?

laserlight

Basically I want to create a relational database, using PHP code. How do I adapt the code posted in my previous post to reflect this?

To create a relational database named db, you would write:

mysql_query("CREATE DATABASE db", $con);

That's it. To create a table named persons with a column named name of type text in that database, you would select the database, and then write:

$sql = "CREATE TABLE persons 
(
  name text
)";

mysql_query($sql,$con);

Do you understand so far?

Are you familiar with SQL syntax?

Dysan

Yup I understand. But how do I link the peoples favorite food and drink with their name, without duplicating fields/data?

laserlight

But how do I link the peoples favorite food and drink with their name, without duplicating fields/data?

Now that is a question that has nothing to do with PHP, but with database design and SQL.

There is a many to many relationship between persons and their favourite food and drink, so you need an intermediate table.

persons {id, name}
items {id, name}
favourites {id, person_id, drink_id, food_id}

So, drink_id and food_id would correspond to the id in the items table.

Dysan

Why do I need an items table? Cant the favorite drink and food correspond to the person

laserlight

Why do I need an items table?

Without one you would get duplication of items, which you said you did not want.

Cant the favorite drink and food correspond to the person

They do correspond, via the favourites table.

Dysan

Sorry, my mistake. Duplication of items is OK. What would the database structure look like now?

laserlight

You just need a persons table with two extra columns.

However, why not use the design I suggested?

Dysan

Because then Ill have an extra table for no reason! 🙂

bradgrafelman

No, you'll have an extra table that would make your database design more normalized, efficient, and altogether more intelligent.

What if you decide to change "tea" later on to "iced tea" (to clarify between hot tea, sweet tea, etc.), and let's say 100 people have already chosen tea. Without this extra table, you'd have to update the main persons table and have SQL search through every row, replacing "tea" with "iced tea". If you did have the extra table, however, it simply updates one indexed row in a single table and that's it.

Dysan

good point 🙂

bmaciag

Follow the advice of laserlight. What you want for something like this is to have multiple tables. This will allow you to create a normalized database.

Take a look at this resource: http://www.peachpit.com/articles/article.aspx?p=30885

This is from the author Larry Ullman, and it is a chapter from one of his books. This resource will help you understand normalization, and why it is so useful.

Dysan

Oh, OK Thanks! 🙂