Solved

Multiple Options Database Storage PHP/MYSQL

Posted on 2011-09-07
10
306 Views
Last Modified: 2012-08-14
Good Morning Experts,
I am currently working on a redesign of our website from when I put it together 3 years ago.
So a bit of history:
I put the website together in the matter of about 3 weeks in a rush.
To store the options into the database as quick as possible I used the php explode and implode commands to store the database.
All of the information is stored into a single field in the database.
It makes updating / changing vehicle options terribly inefficient.
It is also not good database practice to store dynamic information into a flat file.

Thoughts on a possible solution:

There should be an options list table in the database.  This table would contain the fields similar to the following:
opt_id – unique identified for each option
opt_name – the name of the option
opt_type – separate options that are available only for trucks/suvs/vans etc.
opt_group – group based on interior/exterior/electronics/etc
opt_order – the order you want them to show up in the list
Then from that list create the list of available options on the vehicle input screen.
Then when the vehicle information is submit the options will be stored to a separate options table where the options specific to the vehicle are stored.
This is where I am coming into the problem on how to store the data, I believe something like the following should work.
vehicle_id – the id of the vehicle
option_id – the option / this is where I have the issue of whether to store the name or the otpion
option_value – 0 = no 1 = yes

So any input would be greatly appreciated.
0
Comment
Question by:shdwmage
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36497533
If I get you correctly, the vehicle_id might be a VIN number.  That describes a specific vehicle.  Each option might be linked to the VIN number via a key to the option.  The option row can have all kinds of interesting information, but you would only need one key for each particular option.

You can tie these tables together with a "junction table" that has only two columns - one for the VIN and one for the option.  This is a many_to-many table that establishes the relationship between the two tables.  With a middle table like this you can answer questions like, "What options are most popular in my fleet?" and "What options does this vehicle have?"
0
 
LVL 2

Author Comment

by:shdwmage
ID: 36497689
Yes the vehicle id is the VIN.  But I label it in the database as vehicle ID because I have to display the word vin in to many places.

The point of the options list isn't so much to be able to search them or use them for anything but displaying to the customer.  It's more to allow for easier changing of options in the case that one of my employees puts something in incorrectly.

My thought process was to set it up so when I do the join and bring the data to the live page it'd be "WHERE OPTION_VALUE = 1" and only selecting the active values.

The problem I was running to in my mind is that if I store the value of the option versus the name of the option it'll either make for a lot more complicated SQL statement, or a whole heck of a lot of checks in the php end.

I only consider myself intermediate at PHP programming at best.  My background in programming is more from FileMaker, VB, Access.  

I don't have a lot of SQL experience, I understand the point of relational databases, but each program is different enough on which is the best way to do it.

What I know is adding more options to a list is a lot easier when the list is stored into a table, and then displayed from the values of that table.

I also know the value of removing as much redundant data as possible.
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 36498253
With a programming background of any kind, this book will be a breeze for you and it will get you up to speed very quickly on the PHP and MySQL part of things.  Very readable with excellent code examples.
http://www.sitepoint.com/books/phpmysql4/

The process of removing redundant data is called "normalization."  This link has some interesting, if slightly philosophical, results.
http://lmgtfy.com/?q=Should+I+Normalize+My+Database

I would store each option in its own row, something like this:

opt_id INT NOT NULL PRIMARY KEY
opt_name VARCHAR(64)

And I would store each vehicle in its own row, something like this:

veh_id INT NOT NULL PRIMARY KEY
veh_name VARCHAR(64)

The junction table would look something like this:

opt_id INT NOT NULL
veh_id INT NOT NULL

Does this make sense to you?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 2

Author Comment

by:shdwmage
ID: 36498756
Ray,
That was the first php/mysql book I read several years ago.  As a matter of fact my current website is built off of the knowledge I got from this book.

I understand the point of the junction table, but I'm not sure that is the best solution here. I feel like it would over complicate code.

Why should I use a junction table in this case?

Also note I will be linking images to the car in a one to many realationship.
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 36499132
I think the junction table would actually simplify maintenance without complicating the code.  I might also consider a junction table for the images, however if there is only one set of images per vehicle, you could carry the veh_id in the image row and it would be easy to select the images associated with any given vehicle.
0
 
LVL 2

Author Comment

by:shdwmage
ID: 36504197
Ray, what would the code to retrieve that information look like?  Just rough, doesn't have to be super specific.
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 36506034
Probably something like this to pick vehicle number 237 (refer to ID:36498253):

$sql = "SELECT veh_id, veh_name, opt_id, opt_name FROM veh_table, opt_table, junction_table WHERE veh_id = opt_id AND veh_id = 237";
0
 
LVL 2

Author Comment

by:shdwmage
ID: 36512124
I won't be able to try this until Monday.  I'll take a look and get back with you.
0
 
LVL 2

Author Comment

by:shdwmage
ID: 36550799
Ya Ray that worked, I probably won't use it, but it worked.
0
 
LVL 2

Author Closing Comment

by:shdwmage
ID: 36550809
It's not exactly what I was looking for, but its the best alternative to what I was going to do.  Thanks again Ray.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do I update select listbox after search 2 46
PHP processing webform 25 38
MySql Qry performance  (Ver 5.5.17) 1 11
SSL unsecure page mystery 17 36
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

731 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question