1 or more tables?

I am thinking about setting up on a web site a review system, where users can review/rate trucks. This would include adding info from text fields, and making selections from drp dowm lists. There will be about 25 total fields of information. Users will also be able to either view all of the reviews, or view by the truck manufacturer. Would I be better off using one table for all the entries, or breaking it into several tables for better query speed? Thnx

a single table is almost never the answer. You need to seperate the reviews from the products from the reviewers, from the manufacturers, etc.

a simple design would be like this
reviewers
=========
id
name
password
email
etc.

manufacturers
=============
id
name
website
etc.

vehicles
===========
id
name
image
manufacturer_id
etc.

reviews
========
id
vehicle_id
reviewer_id
title
rating
description
etc.

when adding more fields, ask yourself does this field pertain to one of the current tables specifically? If not, chances are it should be in a new table.

OK, thats what I was thinking, but can you tell me how to set up the queries for more than one table so that all the info can be shown when a viewer looks at a review? Would it be something like this?

(SELECT * FROM reviews WHERE table_1 AND table_2 AND table_3 etc.)

or

(SELECT * FROM reviews WHERE table_1, table_2, table_3 etc.)

And would I need to set an ORDER BY on the tables?

Reviewers table will be for who submitted the review, the other 3 are obvious ... so it would be .... first select the manufacturer ...

$man = (SELECT * FROM manufacturers WHERE name=$manufacturer);
$manresult = mysql_fetch_array($man, $conn);

$conn being the variable name of your connection ...
then select the vehicles the manufacturer sells ...

$vehicles = (SELECT * FROM vehicles WHERE manufacturer_id=$manresult[id]);
$vehiclesresult = mysql_fetch_array($vehicles, $conn);

Then select the review

$review = (SELECT * FROM reviews WHERE vehicle_id=$vehiclesresult[id]);
$reviewresult = mysql_fetch_array($review, $conn);

And finally, if you want to see who wrote the review ...

$reviewer = (SELECT * FROM reviewers WHERE id=$reviewresult[reviewer_id]);
$reviewerresult = mysql_fetch_array($reviewer, $conn);


For order by, its simple ... at the end of the mysql querys, add ORDER BY id/name etc .... eg:

$review = (SELECT * FROM reviews WHERE vehicle_id=$vehiclesresult[id] ORDER BY id);

I'll give it a shot...thnx

Ok, here is the insert code, and i'm not getting anything to go into the tables. What Am i missing here? It tells me that there is a fatal error where $query4 is at.

[highlight=php]
$query1 = "INSERT INTO reviewer (id, name, location, company, experience) VALUES ('','$name','$location','$company','$experience')";
$query2 = "INSERT INTO man (id, tractor, model, year, ownertype) VALUES ('','$tractor','$model','$year','$ownertype')";
$query3 = "INSERT INTO ptrain (id, engine, enginesize, trans, rears, tires) VALUES ('','$engine','$enginesize','$trans','$rears','$ti res')";
$query4 = "INSERT INTO comfort (id, seats, dashlayout, drivercomfort, visibility, maneuverability, turnradius, backing) VALUES ('','$seats','$dashlayout','$drivercomfort','$visi bility','$maneuverability','$turnradius','$backing ')";
$query5 = "INSERT INTO interior (id, interiorlayout, roominess, storage, bunkcomfort, appliance) VALUES ('','$interiorlayout','$roominess','$storage','$bu nkcomfort','$appliance')";
$query6 = "INSERT INTO exterior (id, bodydesign, mirrors, frontend, exteriorstorage, rearcab, engineaccess) VALUES ('','$bodydesign','$mirrors','$frontend','$exterio rstorage','$rearcab','$engineaccess')";
$query7 = "INSERT INTO maint (id, warranty, parts, service, dependability, affordability) VALUES ('','$warranty','$parts','$service','$dependabilit y','$affordability')";
$query8 = "INSERT INTO general (id, trailer, mileage, bestfeatures, worstfeatures, recommend, overall) VALUES ('','$trailer','$mileage','$bestfeatures','$worstf eatures','$recommend','$overall')";
$result1 = mssql_query($query1);
$result2 = mssql_query($query2);
$result3 = mssql_query($query3);
$result4 = mssql_query($query4);
$result5 = mssql_query($query5);
$result6 = mssql_query($query6);
$result7 = mssql_query($query7);
$result8 = mssql_query($query8);

mysql_close();
?>

Never mind, I just found the error in it. Now just need to work on displaying it all.

I have the data going into the database, tables and fields correctly. My problems is getting it to display in a results query. Basically, I just want, for now, a simple page to show all of the reviews, and to display each review with all of its corresponding data. I was hoping that the following script would pull it up, but for some reason I'm getting nothing. Do I need to use joins, or simply restructure the select query?



It probably isn't pretty, but I've used this basic code for single tables, but I'm lost with multiple tables. Thnx for any ideas.

first of all, you are getting confused with the reason for multiple tables. From what I can see you have a bunch of tables that could easily be one table interior, exterior, ptrain, maintenance, general etc. all have data that will refer to a single vehicel and there will be only one of each per vehicle. They are specific to the vehicle and could all be in one table `vehicle`. The power of a database is in the word relational that's why it's called RDBMS or RDB. You tie things togetherin relations by using foreign keys. Some relations are 1 to 1 some are 1 to many and some are many to many.

each review will probably have 1 reviewer, but each reveiewer might have many reviews. Each vehicle might have many reviews but each vehicle will have only one manufacturer. Each manufacturer might have multiple vehicles. etc. etc. It's how these relations are configured that make up the power of a database.

Take the realtion of vehicles to reviews. Because each vehicle might have multiple reviews you put the vehicle_id in the review table so that you can see what vehicle the review is about and pull back as little or as much detail about the vehicle as needed when displaying a review and yet even with 100 reviews on the same vehicle all the data about the vehicle is stored in one and only one location which is referenced by the foreign key vehicle_id in the review table. Same thing with manufacturer. each manufacturer might have multiple vehicles but each vehicle has one manufacturer so you link them with a foreign key manu_id in the vehicle table. If you tried to link them in the manufacturer's table by having a column (vehicle_id) what would you do when you had 2 vehicles by the same manufacturer? Even if you made 10 columns in the manufacturers table what would you put in them when there was only 1 vehicle made by them? What happens when they get to the 11th vehicle? Anytime you are might have too many fields or not enough fields you are almost certainly doing it wrong.

Look around the web for some material on database normalization and design.