Inventory System in PHP?

When dealing with data that is nested inside other data I create 2 tables that are in BCNF and have the 2nd table use a foreign key to reference the first one.

For example, if you need wanted to make a system to store Actors and all the Movies they have starred in, you shouldn't store that information in 1 table. Tables are good for storing single-data entries for a given entity, for example an actors first name, last name, age, date of birth, etc. Basically stuff actors only can have 1 of (you can't have more than 1 age/name/etc). Sure, you could also make a column "movie", but that would only store 1 movie. So if the Actor has starred in 50 movies you'll end up with a messy table with 50 "movie-name" columns, and if you want to add the 51st you would have to go and edit the structure. -- not a good approach.

Another alternative would be to store all movies into just 1 column and separate them by commas and then when you load the data just split whenever a comma is encountered. But what do you do if you also want to save a movies release date, genre, imdb rating, etc? You get another problem...

So the best way, in my opinion, to solve this is by making 2 tables. Like this:

  ACTORS (id, first_name, last_name, age, country_of_residence)

--pretty straight-forward. The id is just a unique identifier that counts from 0 for each entry you add.

  MOVIES (id, actor_id, movie_name, release_date, genre, rating)

--as before the id here is a unique id for the movie. The actor_id points to the id of the ACTOR table and as such "links them" in a way. (Let's assume that we only save the protagonist of the movie, and not all actors).

When creating the MOVIES table, you need to make the actor_id a foreign-key (a reference basically). Also have the MOVIES table on update cascade and on delete cascade - this will update/delete all movies if their Actor has been updated/deleted.

So similarly for your program, you would have 1 table to save data about each graph (name, x-axis label, y-axis label, and any settings it may have), and another to store the "dots" on each graph which has a foreign-key pointing in which graph each dots "belongs to". (So if you delete Leonardo Di Caprio - the movie Titanic will also get deleted automatically - preventing accidental "stray" movies).

Read more about Foreign Keys here and how to create such table: http://www.w3schools.com/sql/sql_foreignkey.asp If you do some research on google, you'll find a ton of info.

Hope this makes sense. :)