• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 789
  • Last Modified:

ColdFusion/MySQL Searching database using dynamic multiple related selects

Using ColdFusion, I need to figure out how to do the three types of searches, data display and sort, found as an example on this page: http://www.lisaturn.com/search.asp. I need to create a similar page using similar data, and the searches will be identical to these examples using used cars.

I know how to create a CF database application and display data from the database, as well as create a password protected Admin display page of the full database data with an ADD, UPDATE or DELETE button so that the admin can manage the database data (if it's all in one table) I actually already have that part of the application completed. I also know how to display that data on a web page. What I don't know how to do, is to search the database with these dynamic multi selects (Make and then specifi Models), or how to display the multiple record counts (Under Search by Category), or how to sort the resulting searches (Sort Vehicles By). I was hoping to tackle each function one at a time, beginning with the searches if you could help me.

To begin, I first need to know if these searches can be done using one MySQL database table that includes all the information about each used car, or... QUESTION ONE: will I need to have a separate table for Make, one for Model, and one for Category? QUESTION TWO: The other database design question that I have is if there needs to be additional tables to normalize the database, then which table gets the foriegn key? I am always confused about that.

Currently, below is the one and only table I've been trying to work with, so far to figure out how to create these search elements:

TABLE NAME: SHM_Used_Cars

SHM_UsedCarsID
SHM_PhotoName
SHM_CarStyle
SHM_CarEngine
SHM_CarTransmission
SHM_CarExteriorColor
SHM_CarInterior
SHM_CarStockNum
SHM_CarMileage
SHM_CarPrice
SHM_CarSpecialPrice
SHM_CarComments
SHM_CarVINNum
SHM_CarYear
SHM_CarMake
SHM_CarModel
SHM_CarCategory
DateTime

After I determine how many tables I will need, then I will proceed to ask about how to accomplish the remaining search functions, one at a time. I say one at a time because being self-taught, I need to figure out these tasks as I'm able to comprehend and learn what's going on. If I am over-whelmed with too many instructions at once, I just get confused and will waste both your time and mine. Therefore, I am seeking a solution one step at a time, if you don't mind helping me.

I should also mention that I've been trying to figure this out myself for over two weeks now. I've tried multiple examples that I've found on this site and others, but have not been able to get anything to work. I can show you one of the examples that I have so far, if you need me to, but I just think I should start over, if you don't mind helping me.


The attached code is one of the examples I am working on, but the process page is not working right, so I think I need to start over.

Your help is greatly appreciated. Thank you so much.


<cfquery name="q_all_makes" datasource="designpubdemo" username="elizabeth3" password="webdesyne3">
SELECT DISTINCT SHM_CarMake
FROM SHM_Used_Cars
ORDER BY SHM_CarMake
</cfquery>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title></title>
</head>

<body>
<form id="form1" name="form1" method="post" enctype="multipart/form-data" action="search_new_process4.cfm">
<select name="SHM_CarMake">
<option value="0">Select Vehicle Make</option>
<cfoutput query="q_all_makes">
<option value="#SHM_CarMake#">#SHM_CarMake#</option>
</cfoutput>
 </select>
<input type="submit" name="button" id="button" value="Search" />
</form>
</body>
</html>

Open in new window

0
Elizabeth2
Asked:
Elizabeth2
  • 23
  • 23
18 Solutions
 
Elizabeth2Author Commented:
I have finally got part of one search task to work where it correctly identifies the correct number of resulting rows, and displays the correct number of records, here: http://www.designpubdemo.com/SHM/search.cfm 

But I still don't know how to append my existing code to populate the second select drop down list which would present the specific the Models, based on which Car Make was selected (which is not included on this test search page) Also, to get this to work, I hard coded the values for the Makes within my form. I have included the code for both pages, attached as one file. You will need to split them up into the respective pages, as noted in the comments.

I'm leaving for the day, but will return in the morning to continue working on this task. Thank you for any help you can provide.
<!-- BEGIN the page search.cfm --->

<style type="text/css">
<!--
.style1 {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-weight: bold;
	font-size: 12px;
}
-->
</style>
<table width="200"  border="0" align="center" cellpadding="0" cellspacing="0">
  
  <tr>
    <td align="left" valign="top"><form method="POST" action="search_admin.cfm">
<table width="200" border="0" cellspacing="2">
<tr>
  <td><span class="style1">Make</span></td>
  </tr>
<tr>
  <td>
  	<select name="SHM_UsedCarsID" id="smalltext">
			<option value="0">Select Vehicle Make</option> 
			<option value="1">BMW</option> 
			<option value="2">Ford</option>
            <option value="3">GMC</option>
            <option value="4">Chrysler</option>
            <option value="5">Toyota</option>
            <option value="6">Chevrolet</option> 
			<option value="7">Acura</option>
			
	</select>  </td>
</tr>
<tr>
  <td><span class="style1">Model</span></td>
  </tr>
<tr>
	<td><input name="SHM_CarMake" type="text" id="SHM_CarMake" size="22" maxlength="10"></td>
</tr>
<tr>
    <td align="left"><input type="submit" value="search" id="searchbutton"></td>
</tr>
</table>
</form></td>
  </tr>
</table>

<!-- END the page search.cfm --->

<!-- BEGIN the page search_admin.cfm --->
<cfparam name="q_search" default="No search performed.">

<cfquery  name="q_search" datasource="#datasource#">
SELECT 
SHM_Used_Cars.SHM_UsedCarsID,
SHM_Used_Cars.SHM_PhotoName,
SHM_Used_Cars.SHM_CarYear,
SHM_Used_Cars.SHM_CarMake,
SHM_Used_Cars.SHM_CarModel,
SHM_Used_Cars.SHM_CarStyle,
SHM_Used_Cars.SHM_CarEngine,
SHM_Used_Cars.SHM_CarTransmission,
SHM_Used_Cars.SHM_CarExteriorColor,
SHM_Used_Cars.SHM_CarInterior,
SHM_Used_Cars.SHM_CarStockNum,
SHM_Used_Cars.SHM_CarMileage,
SHM_Used_Cars.SHM_CarPrice,
SHM_Used_Cars.SHM_CarSpecialPrice,
SHM_Used_Cars.SHM_CarComments,
SHM_Used_Cars.SHM_CarVINNum,
SHM_Used_Cars.MakeIDFK, 
SHM_Make.MakeID

FROM SHM_Used_Cars, SHM_Make
WHERE SHM_Used_Cars.SHM_CarMake LIKE '%#form.SHM_CarMake#%'
<cfif form.SHM_UsedCarsID NEQ "0">
AND SHM_Make.MakeID = #form.SHM_UsedCarsID#</cfif>	
AND SHM_Used_Cars.MakeIDFK = SHM_Make.MakeID
</cfquery>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title></title>
<style type="text/css">
<!--
.style1 {font-size: 12}
-->
</style>
</head>

<body>
<br />
<br />

<br />

<table width="100%"  border="0" align="center" cellpadding="0" cellspacing="0">
              <tr>
                <td width="50%" align="center" valign="middle"><table width="100%"  border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td class="results"><!-- If there are no Used Vehicles, display a message. --->
	      <!--- If there are no Used Vehicles, display a message. --->
      <cfif q_search.RecordCount EQ 0>
         There are no results to display.

      <!--- Otherwise, display the returned data. --->
      <cfelse>
         <!--- In a cfoutput block, display the record count --->
         <cfoutput>
            <i>Found #q_search.RecordCount# results.</i><br>
         </cfoutput>

      </cfif></td>
  </tr>
</table></td>
                <td width="50%" align="center" valign="middle">
				<!--- Back to previous page --->
				<FORM>
                <INPUT TYPE="BUTTON" VALUE="Search Again" ONCLICK="history.go(-1)"></FORM></td>
              </tr>
</table>
<table width="2000" cellspacing="0"  class="record" style="margin-left:8px; width:2000px;">


<cfoutput>
<tr>
          <td width="2%" class="data_titles"><span class="column_title"><strong>Check</strong></span></td>		 
          <td width="4%" class="data_titles"><span class="column_title"><strong>Photo</strong></span></td>
          <td width="3%" class="data_titles"><span><strong>Year</strong></span></td>
           <td width="4%" class="data_titles"><span><strong>Make</strong></span></td>
           <td width="5%" class="data_titles"><span class="column_title"><strong>Model</strong></span></td>
            <td width="5%" class="data_titles"><span class="column_title"><strong>Style</strong></span></td>
            
            
<td width="4%" class="data_titles"><span class="column_title"><strong>Engine</a></strong></span></td>
<td width="5%" class="data_titles"><span class="column_title"><strong>Transmission</a></strong></span></td>
<td width="6%" class="data_titles"><span class="column_title"><strong>Exterior Color</a></strong></span></td>
<td width="5%" class="data_titles"><span class="column_title"><strong>Interior</a></strong></span></td>
<td width="6%" class="data_titles"><span class="column_title"><strong>Stock Number</a></strong></span></td>
<td width="5%" class="data_titles"><span class="column_title"><strong>Mileage</a></strong></span></td>
            
           
        <td width="5%" class="data_titles"><span class="column_title"><strong>Price</strong></span></td>
           <td width="5%" class="data_titles"><span class="column_title"><strong>Special Price</strong></span></td>
          
<td width="20%" class="data_titles"><span class="column_title"><strong>Comments</a></strong></span></td>
<td width="6%" class="data_titles"><span class="column_title"><strong>VIN Number</a></strong></span></td>
</tr></cfoutput>
        <!--- Displaying query for list and using cfif and cfelseif to define the background color based on ID --->
        <form action="display.cfm" method="post">
        <input type="hidden" name="submitted_1" value="yes">
    <cfoutput query="q_search">
      
<cfif q_search.currentrow mod 2>
<cfset rowclass = "odd">
<cfelse>
<cfset rowclass = "even">
</cfif>
<tr class="#rowclass#">

<td align="center" valign="middle"><input name="SHM_UsedCarsID" type="radio" value="#SHM_UsedCarsID#" checked="checked" /></td>
              <td class="email"><img src="/upload_photos/#SHM_PhotoName#" width="291" height="117" hspace="5" vspace="5" align="left" /></td>

              <td class="email">#SHM_CarYear#</td>
			  <td class="email">#SHM_CarMake#</td>
			  <td class="email">#SHM_CarModel#</td>
			  <td class="email">#SHM_CarStyle#</td>
              <td class="email">#SHM_CarEngine#</td>
			  <td class="email">#SHM_CarTransmission#</td>
              <td class="email">#SHM_CarExteriorColor#</td>
              <td class="email">#SHM_CarInterior#</td>
              <td class="email">#SHM_CarStockNum#</td>
              <td class="email">#SHM_CarMileage#</td>
              <td class="email">#SHM_CarPrice#</td>
              <td class="email">#SHM_CarSpecialPrice#</td>
              <td class="email">#SHM_CarComments#</td>
              <td class="email">#SHM_CarVINNum#</td>
      </tr>
          </cfoutput>
          <tr align="left">
            <td colspan="8" valign="middle">&nbsp;</td>
          </tr>
        </form>
</table><br /><br />
<br />
<br />
<br />
<span class="style1"></span>
</body>
</html>

<!-- END the page search_admin.cfm --->

Open in new window

0
 
_agx_Commented:
Hi Elizabeth2,

That is a LOT of ground to cover.  So this is not really something that can (or should be) be answered all in one question.

But first, I think you should backup and focus on implementing a good database design. Though you could use a single table, multiple tables are far better for both performance and data integrity. There are a few ways you could slice the data.  It depends. So I'll just suggest a general approach.  (I'm _not_ an auto aficionado, so take my assumptions about autos with a grain of salt ;-)

Start by thinking about the info the application will handle, and break it down into: entities, attributes and relationships.  Entities are typically the major objects, and things that can be uniquely identified.  

  • A CAR "is a" vehicle
  • A MAKER "is a" manufacturer of cars  (Ford, Honda, etc..)
  • A MODEL "is a" line of car produced by a manufacturer  (Accord, Explorer, etc..)
  • A STYLE "is a" specific style of car (2-Door, Truck, Sedan, SUV, etc..)
Next, think about how those entities/attributes relate to each other
  • A MAKER produces one or more MODELs
  • A MODEL "has a" single MAKER
  • A CAR "has a" single MODEL number
  • A CAR "has a" single STYLE
Then construct those relationships:

MAKER           /   Columns: MakerID (PK), MakerName (Unique)
MODEL          /   Columns:  ModelID (PK), ModelNum (Unique),  MakerID (FK)
STYLE            /   Columns:  StyleID (PK), StyleName (Unique)
CAR               /   Columns:  CarID (PK), VINNum (Unique),  ModelID (FK), StyleID (FK)
Next think about the additional attributes (or properties). Determine which ones are important enough to store in a separate table.  "Color"  might be a candidate, as it might be a popular search field.  But it all depends on the needs of your application.  Keep in mind it's a balancing act. There is no one right answer.  

  • - Price   (number)
  • - Color  (Possibly separate table?)
  • - Mileage (number)
  • - Year  (number)
  • - EngineType  (Possibly separate table ?)
  • - Photo (string path)
  • - Certified  (bit flag)
  • - Comments (string)
  • etc..
The structure of the search filters is a tricky one.  How you want to handle the front end is a question for later.  But my gut is saying don't use a single "Category" filter. Instead, use separate filters. So you would have

  • "Make" - The options would come from your MAKER table (list)
  • "Model" - The options would come from your MODEL table (list)
  • "Style" - The options would come from your STYLE table (list)
  • "Price" (list) You could either hard code the options, or  create a separate table to use for searching (more dynamic).
  • "Certified" could be a checkbox  (but that is more of a front end issue)
I usually create a table like this for searching on a ranges like price:

             PRICERANGE         / Columns: PriceRangeID (PK), RangeMin (number), RangeMax (number)

Then you can easily use it in a JOIN to find items within the selected range

           SELECT  c.CarID
           FROM     CAR c INNER JOIN PRICERANGE r  ON c.Price BETWEEN r.RangeMin AND r.RangeMax
           WHERE   r.PriceRangeID = #selectedPriceRange#
           .....

Anyway, once you get a good table structure implemented.  The rest will start to fall into place.  Definitely keep the front end search interface in mind, just do not let it totally dictate how you design the database.  

HTH





0
 
Elizabeth2Author Commented:
Hi HTH,
Thank you so much! This is exactly what I needed for this part of the process.

Instead of a separate table for Style, I chose to create a table named Category, since that is one of the searches performed on my example site: http://www.lisaturn.com/search.asp, and Category is really being used as the "Style" filter, just named differently. There is a car style, but it is only an attribute in the Car table, and is not used as a search filter. As seen in the example site, Category has these attributes, so I've created a Category table with these same values:
CategoryID CategoryName
1 Certified
2 Coupes
3 Sedans
4 SUVs
5 Trucks
6 Under $10,000
7 $10,000 - $20,000
8 $20,000 and Up

I'm not sure why the developer of the example site decided to create this kind of search, because a car could clearly have more than one of these "Categories," which is confusing, but my task is to duplicate this example site, with a better visual layout and design for the display, but functionally the same, so I went with a Category table using the same values of the example site.

I'm trying to keep this as simple as I can, so that when I'm creating the display pages, I will be able to keep up with all the queries that will be necessary to pull all of this together. I'm not that experienced with JOINs, though I have used them once or twice in the past, it's been a while since I had to wrap my brain around the concept, so simple is better when possible.

I have created and populated the tables as you suggested, following the model you outlined above. I also created the PriceRange table as suggested. I'm not quite sure how to populate it at this point, as I'm not completely sure how this will be used, but am hopeful that you will elaborate as I progress, that is, if you are willing to continue to help me. And, that is my next question. Are you willing to continue to help me with these tasks? I sure hope so. It seems you understand exactly what I need to do.

Let me thank you once more, and please let me know what you suggest would be my next step.

As I had previously mentioned, and had previously attached the code, I have the one (Search) select list working here: http://www.designpubdemo.com/SHM/search.cfm, but was hoping you could help me to create the second dependent "Model "select list based on the new tables and dynamically fill in the "Model" select list based on which Make was selected. Also, to get my test example to work, I had to hard code the "Models" into my form. I'm hoping there is a better way to dynamically populate that form and select lists, (please see the previoulsly attached code) but all of my attemps to dynamically create the select list, either reproduced multiple "Makes" (multiple Ford, multiple GMC, etc...within the list, or on the display page, I got the right number of records, but they were not matching the correct data - very frustrating)

I now have these tables:
 SHM_Category
 SHM_Make
 SHM_Model
 SHM_PriceRange
 SHM_Used_Cars

I have put a FK column for MakeIDFK, Model IDFK and CategoryIDFK into the Used_Cars table, but I'm aware that Model is a subset of Make, so I also have the ModelIDFK in the Make table. My question is, since it is already in the Model table, should I delete it from the Used_Cars table? I put it in both because I wasn't sure how I'll eventually pull that information out, so could you help me understand this relationship?

this is my current understanding of the tables:
 SHM_Used_Cars
 SHM_Category
 SHM_Make
 SHM_Model
 SHM_PriceRange


Until I hear back, I will be attempting to figure this out, but I do think this is over my head, and will need your help, please.

Gratefully,
elizabeth ;-)
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Elizabeth2Author Commented:
Whoops! I hit the wrong key and submiited my comment before I was finished. I was working on the paragraph that starts with...
this is my current understanding of the tables:
 SHM_Used_Cars
 SHM_Category
 SHM_Make
 SHM_Model
 SHM_PriceRange

What I was trying to write was that I understand that the SHM_Used_Cars table has a relationship with Make and Category and that Make has a relationship with Model. I am attaching my database tables for you, so you can see what I have so far.

Will you please let me know if these are correct?

Thank you so much.
elizabeth
TABLE: SHM_Used_Cars

SHM_UsedCarsID smallint(5)   UNSIGNED No  auto_increment               
SHM_PhotoName varchar(100) utf8_general_ci  Yes NULL                
SHM_CarStyle varchar(100) utf8_general_ci  Yes NULL                
SHM_CarEngine varchar(100) utf8_general_ci  Yes NULL                
SHM_CarTransmission varchar(100) utf8_general_ci  Yes NULL                
SHM_CarExteriorColor varchar(100) utf8_general_ci  Yes NULL                
SHM_CarInterior varchar(50) utf8_general_ci  Yes NULL                
SHM_CarStockNum varchar(50) utf8_general_ci  Yes NULL                
SHM_CarMileage varchar(30) utf8_general_ci  Yes NULL                
SHM_CarPrice varchar(30) utf8_general_ci  Yes NULL                
SHM_CarSpecialPrice varchar(30) utf8_general_ci  Yes NULL                
SHM_CarComments varchar(1500) utf8_general_ci  Yes NULL                
SHM_CarVINNum varchar(30) utf8_general_ci  Yes NULL                
SHM_CarYear varchar(25) utf8_general_ci  Yes NULL                
SHM_CarMake varchar(50) utf8_general_ci  Yes NULL                
SHM_CarModel varchar(50) utf8_general_ci  Yes NULL                
SHM_CarCategory varchar(50) utf8_general_ci  Yes NULL                
DateTime datetime   Yes NULL                
MakeIDFK tinyint(4)   UNSIGNED Yes NULL                
ModelIDFK tinyint(4)   UNSIGNED Yes NULL                
CategoryIDFK tinyint(4)   UNSIGNED Yes NULL 




TABLE: SHM_Make

MakeID tinyint(4)   UNSIGNED No  auto_increment               
MakeName varchar(50) utf8_general_ci  Yes NULL



TABLE: SHM_Model

ModelID tinyint(4)   UNSIGNED No  auto_increment               
ModelName varchar(50) utf8_general_ci  Yes NULL                
MakeIDFK tinyint(4)   UNSIGNED Yes NULL




TABLE: SHM_Category

SHM_CategoryID tinyint(5)   UNSIGNED No auto_increment               
SHM_CategoryName varchar(50) utf8_general_ci  Yes NULL





TABLE: SHM_PriceRange

PriceRangeID tinyint(4)   UNSIGNED No auto_increment               
RangeMin int(16)   Yes NULL                
RangeMax int(16)   Yes NULL

Open in new window

0
 
Elizabeth2Author Commented:
Hi HTH,

I have made some progress in the way of the layout and display of the data. Please see this new page which has the same funtional code, but has been formatted and styled: http://www.designpubdemo.com/SHM/search_layout.cfm

Are you able to contintue to help me today?

elizabeth
0
 
_agx_Commented:
... but my task is to duplicate this example site

Yes, I agree the current options are confusing, and I suspect duplicating the style will make the code a bit more convoluted.  But, just because the current site is presenting the options that way, doesn't mean the old db has to store the info that way.  Even if you do keep the current "Categories" table, I really don't think you should store a key for it in the CAR table.  As you say, a car could clearly have more than one category.  If you use a single key, the db would be missing a lot of info.  Not to mention it will very easily get out of synch or corrupted.  

For example, what if the admin enters a car with the price 15,000, yet selects the category 20,000 and over.  Or enters a truck but selects the category "Sedan"? It is a duplication of information, which you want to avoid whenever possible because that usually leads to inaccurate data and bad search results, which frustrate users.

My suggestion would be to store the various properties separately:

  • CAR.TYPE
  • CAR.PRICE
  • CAR.CERTIFIED
Then in your search interface, parse the user-selected filter. Use a bit of CF code to construct the needed SQL code to search those 3 fields. (More about that later) It's a bit more convoluted, but it will work and will preserve the current design. More importantly, it will allow you to store all of the CAR properties correctly, and minimize inaccuracies.

... so simple is better when possible.

Agreed. But though you may find some of the queries unfamiliar at first, it is very important to structure the tables and queries well. Otherwise the site will scale poorly, and as the db grows, searching will become slower and slower.

... And, that is my next question. Are you willing to continue to help me with these tasks? I sure hope so. It seems you understand exactly what I need to do.
Sure. But I would keep this thread focused on db design. Then open new questions once you get to the iterface. It is more in line with EE's policy ie One question per thread. (Probably because it makes it easier for people to find answers to specific problems when threads stick to one specific topic - and I agree with that)

Next
Let me review the table structure you posted. I have see a few few issues. So let me write up my comments and suggestions about those separately.
0
 
_agx_Commented:
@Elizabeth2,

Quick question about the table layouts. Is size an issue? If not, some of the columns definitely need more room to grow.

0
 
Elizabeth2Author Commented:
First, do I have your name correct? Is it HTH or _agx_? I didn't want to seem stupid and/or disrespectful and use the wrong name. ;-)

Second, thank you. You don't know how grateful I am for your help.

Third, your points are excellent. I do understand most of what you've explained, except for the part about "parse the user-selected filter." I will definately need your future help with that query. I guess my struggle is that I don't know how to write the complex queries that will pull the multiple tables together for display and/or administration, so I tend to only use one large table. I do know how to query more than one table, but another reason that I tend to use only one is because I also have to create an Admin backend for the client to manage the database. I've done this before with simpler tables that didn't have searches. It's easier to just insert or update one table. I've used GetMaxID to insert into a second table, but only once a couple years ago, and I'm still not sure if I did it the best way, but it did work. I sort of stumbled through that one. Being self-taught is so hard sometimes. Finding this website has been awesome!

I have split up the question as you suggested. I posted it a few minutes ago to address just the multiple select lists search feature.

I will go back now and emplement your database suggestions, and am available all day to continue with any suggestions you might have to futher tweak the database. This is so FUN! Thank you again.

Am I correct that "Car.Certified" would just be a Yes/No option in MySQL?

Also, concerning this statement, "If you use a single key, the db would be missing a lot of info.  Not to mention it will very easily get out of synch or corrupted. " so I would just have the Category Table on it's own, no relationship in the database structure, but when that data needs to be extracted, I would be using CF and Queries? Is that correct? In other words make the code do the work, instead of the database structure? Let me know if I'm out in left field.

elizabeth ;-)

0
 
_agx_Commented:
A couple thoughts about the tables

  • Unless space is an issue, don't make the sizes too small. Make sure you've allotted enough capacity and leave yourself  room to grow.
  • Consider making some of the CAR feature columns (engine, color, etc..) either a FK or an ENUM.  
  • (This is mainly a personal preference) Prefixing the table names is a good idea. However, you don't need to re-prefix all of the columns with "SHM" or duplicate the car table column with "Car". Descriptive names are good, but you'll find things get long and unwieldy when prefixes are repeated multiple times.
  • If a table has a foreign key (like CAR model number), don't have two columns for it. Just use the numeric value of the record ID from the MODEL table only
TABLE: SHM_Model

ModelID INT   UNSIGNED No  auto_increment              
ModelName varchar(50) utf8_general_ci  NOT NULL          
MakeID INT (FK)   NOT NULL
            * Model name and the FK MakeID should never be null.
             * Model name must also be unique, or you'll have data integrity problems.

TABLE: SHM_STYLE

StyleID INT UNSIGNED No auto_increment
StyleName varchar(50) utf8_general_ci  NOT NULL

            * Style name should never be null (or how would users search by it ;-)
             * Style name should always be unique

TABLE: SHM_Used_Cars

UsedCarsID INT  UNSIGNED No  auto_increment              
VINNum varchar(30) utf8_general_ci  Yes NULL               ** are there any special rules about VIN #'s?
Engine varchar(100) utf8_general_ci  Yes NULL                
Transmission varchar(100) utf8_general_ci  Yes NULL                
ExteriorColor varchar(100) utf8_general_ci  Yes NULL                
Interior varchar(50) utf8_general_ci  Yes NULL                
StockNum varchar(50) utf8_general_ci  Yes NULL                
Mileage MUST BE SOME NUMERIC TYPE Yes NULL             ** Price and mileage should always be numbers.
                                                                                                          ** Otherwise, you won't be able to search. Use a numeric type
Price MUST BE SOME NUMERIC TYPE  Yes NULL                
SpecialPrice MUST BE SOME NUMERIC TYPE Yes NULL                
PhotoName varchar(255) utf8_general_ci  Yes NULL                
Comments varchar(1500) utf8_general_ci  Yes NULL                
ModelYear MUST BE NUMERIC TYPEYes NULL                
MakeID INT (FK) NOT NULL
ModelID INT (FK) NOT NULL
StyleID INT (FK) NOT NULL
CreatedDate datetime NOT NULL?

* MakeID, ModelID, StyleID should all be foreign keys enforced with a constraint
* "DateTime" is a reserved word. It's better to choose another name like "CreatedDate". In which case, it shouldn' t be null
0
 
Elizabeth2Author Commented:
Thank you so much. This gives me a lot to do. I  will post back once I've completed these suggestions and insights.

I'm sorry to say that I don't know anything about ENUM. I found this explaination below, but have no idea how it is applied when pulling data from the database to display or administrate. Nonetheless, I'm hoping to learn and grow, so I will just follow your lead where indicated and change the TYPE as suggested.
===================
ENUM is short for ENUMERATED list. This column can only store one of the values that are declared in the specified list contained in the ( ) brackets.
ENUM('y','n') You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.

SET is similar to ENUM except SET may contain up to 64 list items and can store more than one choice.
===================

Thank you,
elizabeth
0
 
_agx_Commented:
I'm sorry to say that I don't know anything about ENUM

      Yes, those descriptions are terrible.  The sole purpose of ENUM and SET is to limit what values you can enter into a string column.
       For example, if you only wanted to allow the values "Sedan, Coupe and Truck" in the StyleName column, you could use an ENUM

                             CREATE TABLE CAR (  StyleName  ENUM('Sedan', 'Coupe', 'Truck')  , ... other cols )

       So if you tried to enter a wrong value into that column like "Whatchamacalit", it would be rejected.  

       But those are more advanced concepts.  You might just want to keep it simple and leave them as a basic varchar column
       for now.  The important part is that your ADMIN interface restrict what can be entered into the field.  ie Present a select list
       of x options, not a text field. Otherwise you'll end up with 5,000 variations and misspellings of a "two door" car ;-)
       You can always change this down the road.

do I have your name correct

_agx_ is fine (Thanks for asking). Also, sorry, my timing is off. I keep responding without seeing your latest replies ;-)
so I tend to only use one large table

Yes, everyone does until they start to get comfortable with sql. Then we all realize what the db experts said was true: single tables usually complicate things more. They also lead to poor query performance, because everything tends to be a VARCHAR column. Searching/querying VARCHAR columns is typically slower than the same operation on a numeric column.
and am available all day to continue with any suggestions you might have to futher tweak the database. This is so FUN!
That's great! I am so glad you are enjoying it. I am available most of the day too. Though there might be slight delays in my responses.
Am I correct that "Car.Certified" would just be a Yes/No option in MySQL?

Yep
> so I would just have the Category Table on it's own, no relationship

Exactly. (You could store it in CARs, but I do not see any positive benefit to doing so. Quite the opposite). Like I said, I think this new option is not as clean as the original structure I proposed. But if you must stick with the original design, this is a doable alternative. Mainly you'll be taking the selected option and parsing it into several values you can use in your search queries.  Again, not my preference. But definitely reasonable.

ie:  form.selectedCategory => transform into a few variables you can use in your query. Just psuedo-sql, but something like

WHERE  Certified = #certifiedOption#
AND    Price BETWEEN #minValue# AND #maxValue#  
AND    StyleID = #selectedStyleType#
...

0
 
_agx_Commented:
> Am I correct that "Car.Certified" would just be a Yes/No option in MySQL?> Yep

      Correction: Technically it would be a BIT or BOOL field in MySQL terms (not a varchar field)
0
 
_agx_Commented:
... Model name must also be unique, or you'll have data integrity problems.

BTW: I got to thinking about that comment of mine. Maybe it would be more accurate to say the _combination_ of model name and MAKERID should be unique.  (Like I said, I'm definitely not an auto afficiando ;-)  Anyway, probably not an issue for now. The db is small and new. But something to keep in mind.
0
 
_agx_Commented:
My question is, since it is already in the Model table, should I delete  it from the Used_Cars table? I put it in both because I wasn't sure how  I'll eventually pull that information out, so could you help me  understand this relationship?

          Sorry. I forgot about this question (and the MAKE table).  You are correct. Since MAKE is related to car via the MODEL table.
          You don't need to include the MAKEID in the CAR table.  You could.... some db's deliberately duplicate keys that way to
          simplify JOINs.  But I've always preferred not to because it can create inconsistencies.


          TABLE: SHM_Make

MakeID INT   UNSIGNED No  auto_increment              
MakeName varchar(50) utf8_general_ci NOT NULL

     * Make name must be unique and should never be null
         To access both CAR and MAKE information, you will  need a JOIN between the three tables.
          (Don't worry, this  is totally normally in a good database.)   Completely untested, but the SQL

SELECT  c.UsedCarsID, m.MakerName, mo.ModelName
FROM    SHM_Used_Cars c
                INNER JOIN SHM_Model mo ON mo.ModelID = c.ModelID
                INNER JOIN SHM_Make m ON m.MakeID = mo.MakeID
0
 
Elizabeth2Author Commented:
Hi _aqx_,

I've been going through all of your suggestions and have these questions and answers to your question:
You wrote...
A couple thoughts about the tables

Unless space is an issue, don't make the sizes too small. Make sure you've allotted enough capacity and leave yourself  room to grow.

No, size is not an issue as far as I know, but just so I understand, do you mean the number of characters that I alloted for each column? What do you mean by "room to grow?" I just picked a number of characters such as (50) because I don't know of any car Model name, for instance, that would be longer than 50 characters. That's how I usually determine that number. Am I misunderstanding something about setting or defining that number? I had taken an online beginner database design course, and nothing much was covered concerning that particular topic, so I'm just winging it when picking these numbers. Anything you can tell me that would help me understand better would be greatly appreciated. When you say "grow" what do you mean specifically? Grow in the number of characters? Can you please give me an example?
 
I have made the changes to the SHM_Make table as suggested, and am just following each thread to make sure I emplement all your suggestions:
You wrote (and I have completed this change):
TABLE: SHM_Make

MakeID INT   UNSIGNED No  auto_increment              
MakeName varchar(50) utf8_general_ci  NOT NULL

    * Make name must be unique and should never be null

 
 -------------------
Thank you,
elizabeth ;-)
0
 
Elizabeth2Author Commented:
Hi _aqx_,

I'm now looking at another suggestion you gave me:

Then construct those relationships:

MAKER / Columns: MakerID (PK), MakerName (Unique)
MODEL / Columns: ModelID (PK), ModelNum (Unique), MakerID (FK)
STYLE / Columns: StyleID (PK), StyleName (Unique)
CAR / Columns: CarID (PK), VINNum (Unique), ModelID (FK), StyleID (FK)


I have made the Name columns Unique for each Model, Make and Category as indicated above, but I have this question... In MySQL I don't have a similar option (located along with the options for Primary Key, Unique and Index) to make ModelID, or MakeID, or CategoryID a (FK). There is no option for that term, so for now, I selected "Index." Is that ok or right? Am I missing something in how that is supposed to be defined in MySQL?
In my table, I named that column ModelIDFK so I would know it was a FK, but there is no option for a FK that I can see in MySQL. Is that an option in other databases, or am I just missing where that option would be in MySQL? All I'm seeing available in MySQL is Primary Key, Unique and Index.
Thank you,
elizabeth
0
 
Elizabeth2Author Commented:
Hi _aqx_,

You wrote:
> Am I correct that "Car.Certified" would just be a Yes/No option in MySQL?
> Yep

     Correction: Technically it would be a BIT or BOOL field in MySQL terms (not a varchar field)

In MySQL, I went to create the new column in the Used_Cars table. I did not see a BIT type, so I selected BOOL, but when I tried to save it, I got an SQL Error about the TYPE and Number I selected (I tried several different numbers such as 3, 50, 100) and each gave the same error. Finally, to get it to work, I selected VARCHAR and it then saved successfully.
I admit that I don't fully understand all of the TYPEs in MySQL, and what they do or don't do, nor the implications of each, but I have used VARCHAR before for a Yes/No column and in my SQL Queries would do a WHERE statement with LIKE to find the "Yes," or the "No" in a column. I"m sure there are other ways to do this type of column, that I don't yet understand, so if you could please help me understand why it should be BIT or BOOL, and why MySQL wouldn't accept those TYPEs, maybe if I understood what I was doing a little better ;-) I can go back and make changes to that column and get it right. Was my character number the problem? Any help understanding is appreciated.
As aways, thank you so much!
elizabeth ;-)
0
 
_agx_Commented:
What do you mean by "room to grow?"

The "room to grow" comment was directed at the numeric auto_increment columns. Some of the ID columns (MakerID, ModelID, etc...) were originally defined as tinyint (0-255).  Which means if you try to insert more than 255 model number records the insert will fail. Then you'll need to go back and alter the table to make the column larger, modify validation routines, queries etc.  While not catastrophic, it's better to choose the right size up-front.

When selecting the type for an auto_increment column always consider the maximum number of records you think that table will ever hold.  Especially when the column is used as a FK (since the FK column should have the same data type).  For example, is there a reasonable possibility you might have more than 255 model numbers? Most likely yes.  So it's better to err on the side of caution and choose a size that is a little too big, rather than too small. Granted my choice of INT is probably way too much. But numeric types don't take up a lot of space anyway and space is cheap.  So I chose a larger type that would allow the num. of records in the MODEL table to grow without breaking things.

I just picked a number of characters   such as (50) because I don't know of any car Model name, for instance,   that would be longer than 50 characters. That's how I usually determine   that number.

That is a very good approach. But again, you do not want to underestimate.  Not unless the business rules explicitly say the model number can never be more than 50 characters long.  So while you may not need the extra room, I've found it does not hurt to allow a little extra padding in case my size estimates were wrong.  (ie use varchar(100 or 150) instead of varchar(50)).  Many times it is saved me from having to alter the table and redo the queries and code.
I have made the Name columns Unique for each Model, Make

Did you see my later comment about Model number?  I thought about it and it seems possible that two manufacturers _might_ produce the same model number.  So you model number (alone) probably shouldn't be unique.  
 
In my table, I named that column ModelIDFK so I would know it was a FK, but there is no option for a FK that I can see in MySQL.
Again, just a personal preference here, but I tend not to use prefixes/suffixes like FK. Technically there is nothing wrong with it. But my group's coding standards discourage that naming convention for several reasons. I won't go into them, because that is probably not important to you right now ;-)

Back to the question about creating a FK relationship.  It all depends on what MySQL admin interface you're using. I don't know if your interface provides a "wizard" to create FK's. Usually I just create them in SQL, using what is called a CONSTRAINT.  

              http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

Keep in mind you don't _have_ to create a literal FK constraint.  It is a good practice. But your application will still work without them. It just means the burden is on you (the developer) to make sure you only insert valid ModelID's, MakerID's, etc into the various tables.   I'm not saying you shouldn't use them. Just that some people get confused by them. So don't let it hold you up. Assuming you're just working with test data right now. You can always add them later.  
I did not see a BIT type, so I selected BOOL, but when I tried to save it, I got an SQL Error about the TYPE and Number I selected
             Again, that all depends on what MySQL admin tool you're using.  I usually do it with SQL ;-)  Something like

             CREATE TABLE MyTable ( IsCertified BIT )  ... or ...
             ALTER TABLE `shm_used_cars` ADD COLUMN `IsCertified` BIT NOT NULL




0
 
Elizabeth2Author Commented:
Hi _aqx_,
 
This is going to be a long one. I've pasted your suggestions/comments below, made your text italic, and added my comments and changes completed in indented bold text.
Before I begin, I wanted to show you the TYPE options that I have in MySQL, so that you can help me pick the right one. I don't see BIT or NUMERIC in the list. By NUMERIC did you mean I should select "INT?" Sorry, but this is definately a weak spot in my knowledge.

VARCHAR
TINYINT
TEXT
DATE
SMALLINT
MEDIUMINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
DATETIME
TIMESTAMP
TIME
YEAR
CHAR
TINYBLOB
TINYTEXT
BLOB
MEDIUMBLOB
MEDIUMTEXT
LONGBLOB
LONGTEXT
ENUM
SET
BOOL
BINARY
VARBINARY
 
Your wrote:
A couple thoughts about the tables

Unless space is an issue, don't make the sizes too small. Make sure you've allotted enough capacity and leave yourself  room to grow.

In another thread, I asked a question about this one.


 Consider making some of the CAR feature columns (engine, color, etc..) either a FK or an ENUM.  

One reason that I did not break the tables up into FKs, is that this site I'm building is for my boss, to use as a demo site to show potential clients. I don't actually have any data to use to populate these tables other than what I have gleened from that example site I showed you. In other words, I don't know what those features would be, or the different colors, or the different types of engines, etc... The other reason, is that I definately don't know how to build the complex queries it will take to JOIN all of them together, so I will need to lean on your help as this database has grown beyond my CF and SQL knowledge to display and search the data. Actually, the display page that I had working with one search select list (Model) now no longer works because the query isn't right anymore (because I deleted the original Make and Model columns now replaced with FK columns) with the current DB structure. I'll save that for a later question after we get this DB correctly designed.
 (This is mainly a personal preference) Prefixing the table names is a good idea. However, you don't need to re-prefix all of the columns with "SHM" or duplicate the car table column with "Car". Descriptive names are good, but you'll find things get long and unwieldy when prefixes are repeated multiple times.  
You are right. I shouldn't have done that, but now my queries and display pages are using those names, so I'll be sure not to do that in the future.
If a table has a foreign key (like CAR model number), don't have two columns for it. Just use the numeric value of the record ID from the MODEL table only
We had previously discussed this. I had left those columns for reference purposes only so I could populate the FK tables. I have now deleted those duplicate columns and only have the FK colums in the Used_Cars table.

TABLE: SHM_Model

ModelID INT   UNSIGNED No  auto_increment              
ModelName varchar(50) utf8_general_ci  NOT NULL          
MakeID INT (FK)   NOT NULL

            * Model name and the FK MakeID should never be null.
             * Model name must also be unique, or you'll have data integrity problems.

I have made these changes as suggested.

TABLE: SHM_STYLE

StyleID INT UNSIGNED No auto_increment
StyleName varchar(50) utf8_general_ci  NOT NULL

            * Style name should never be null (or how would users search by it ;-)
             * Style name should always be unique

Good point! Thank you.
I don't have a STYLE table, but have used the name CATEGORY to represent that element, but apart from the actual word, I have made these exact changes to the CATEGORY table.




TABLE: SHM_Used_Cars

UsedCarsID INT  UNSIGNED No  auto_increment              
VINNum varchar(30) utf8_general_ci  Yes NULL                ** are there any special rules about VIN #'s?

Sorry, I don't know?? How would I know that?
Engine varchar(100) utf8_general_ci  Yes NULL                
Transmission varchar(100) utf8_general_ci  Yes NULL                
ExteriorColor varchar(100) utf8_general_ci  Yes NULL                
Interior varchar(50) utf8_general_ci  Yes NULL                
StockNum varchar(50) utf8_general_ci  Yes NULL                
Mileage MUST BE SOME NUMERIC TYPE Yes NULL             ** Price and mileage should always be numbers.
                                                                                                          ** Otherwise, you won't be able to search. Use a numeric type

I changed this to "INT," is that the correct numeric TYPE?


Price MUST BE SOME NUMERIC TYPE   Yes NULL      
I changed all of these NUMERIC to "INT," is that the correct numeric TYPE?
         
SpecialPrice MUST BE SOME NUMERIC TYPE Yes NULL                
PhotoName varchar(255) utf8_general_ci  Yes NULL                
Comments varchar(1500) utf8_general_ci  Yes NULL                
ModelYear MUST BE NUMERIC TYPEYes NULL                
MakeID INT (FK) NOT NULL
ModelID INT (FK) NOT NULL
StyleID INT (FK) NOT NULL
CreatedDate datetime NOT NULL?

* MakeID, ModelID, StyleID should all be foreign keys enforced with a constraint

Sorry. What does "enforced with a contraint mean? I currently have them as INDEX and my only other choices in MySQL were PRIMARY KEY or UNIQUE. There was no Foriegn Key option.
 

* "DateTime" is a reserved word. It's better to choose another name like "CreatedDate". In which case, it shouldn' t be null
I've used this before (DateTime) and name it that way because it reminds me which TYPE I used in MySQL to get this part (time and date) to work, not only in the MySQL DB, but in my SQL queries and CF code to display the time and date. I'd rather just leave this column off for now, and tackle it separately, as date and time have always caused me problems and just adds another layer of complexity that isn't really necessary at this point. So for now, I'll just delete this column.

In the past, when I make my database columns UNIQUE, I've had problems getting my queries and code to work and have spent countless hours trying to fix the problems, but when I would remove that contraint, the code would work, so being at a loss of how else to fix the problem, I went with that solution. Now that I have found this site, and know there is someone that I can ask questions of, I feel more confident that I can get this application and DB done right, and learn how to use the constraints AND still have my code to work correctly. I can't think of any examples right now, but this has come up before. Unfortunately, I'm only asked to build CF application one or two times per year, and don't get to work on these applications on a daily basis, so I almost have to relearn each time I begin a new application. I really appreciate your help. Having someone to help is a dream come true!

After I have made sure I've encourporated all your suggestions, I'll repost the tables to see if I have them right.
Thank you so much,
elizabeth ;-)

0
 
Elizabeth2Author Commented:
Hi _aqx_,
You wrote:

"were originally defined as tinyint (0-255). Which means if you try to insert more than 255 model number records the insert will fail."
I had no idea these types had anything to do with "number of records." I was under the impression that they had to do with number of characters or number of place values for a number. This is important to know. Thank you.
You answered concerning this comment:

I have made the Name columns Unique for each Model, Make

Did you see my later comment about Model number?  I thought about it and it seems possible that two manufacturers _might_ produce the same model number.  So you model number (alone) probably shouldn't be unique.
 
In this instance, I was speaking of the Make, Model and Category tables. After the respective ID I had a column for NAME (ModelName, MakeName, CategoryName respectively). I'm not using Model "Numbers" or Make "Numbers" or Category "Numbers," so I'm not sure we are both on the same page with this one. I'm actually amazed at how well we've been able to communicate in writing about such complex concepts. I was thinking you meant that the NAME should be UNIQUE, but I also upon further thought, realize that there will be multiple records with the same NAME such as Make - FORD, or Model - Mustang.  There could be several Fords and several Mustangs so making them UNIQUE wouldn't work, so I'll remove those UNIQUE constraints.
You wrote in answer to this comment:

In my table, I named that column ModelIDFK so I would know it was a FK, but there is no option for a FK that I can see in MySQL.

You are right. I'm using PHP MyAdmin to build and manage this database and not directly sending commands to the database. That's why there is no constraint for FK in this tool that I can see. I was mistakenly referring to this as MySQL when I should have said PHP MyAdmin tool. I guess I haven't learned enough to write my own statments to the database. I've only recently learned to create my tables that way (using statements imported into the PHP MyAdmin tool), but not knowing all the TYPEs and available commands, nor how to ALTER a table I've already created, I'm at a loss to make this change, even if I wanted to. I've only been using the CREATE TABLE commands that I know. Hm...I'll read that link/page you sent. Thank you.  
Should I send you what I have now, as far as the tables are concerned, to see if we're ready to proceed, or if there are more DB edits to make?

I could thank you a thousand times and it wouldn't express how grateful I am for your help.
elizabeth :-)
0
 
_agx_Commented:
In the past, when I make my database columns UNIQUE, I've had problems getting my queries and code to work and have spent countless hours trying to fix the problems, but when I would remove that contraint, the code would work, so being at a loss of how else to fix the problem, I went with that solution.

I am glad you mentioned that. As Experts we know there are usually three ways to do anything: right, workable and wrong. Sometimes we are hesitant to recommend the "workable" way because we know it's not the best way and we don't want people to think that is the way you should _always_ do something.  Of course, sometimes we end up recommending that may be too complicated for the case at hand ;-)

So with that in mind, maybe we should postpone the idea of using explicit FK and UNIQUE constraints for now and accept that you know what the values should be and will code the application accordingly.  You can always add them down the road.

(VINNumber) Sorry, I don't know?? How would I know that?
(Mileage,Price,Special Price) I changed this to "INT," is that the correct numeric TYPE?
       
I don't know the rules for VIN either (I'm definitely not an auto  afficianado ;-) The only thing I would assume is that they must be unique, like a SSN.  

For mileage and prices, the best type depends on the rules for your app.  Prices (money) can definitely have decimals. I don't know about odomoters (ie mileage).  Since it's just a demo, you could probably get away with INT. But if you think the users would be entering decimal amounts, make it NUMERIC or DECIMAL.

I don't have a STYLE table,

              You should, otherwise how will you know what style each car is ? ;-)


0
 
_agx_Commented:
Sorry, I posted before I saw your last response :)


Should I send you what I have now, as far as the tables are concerned, to see if we're ready to proceed, or if there are more DB edits to make?

Yes, please do.  Does PHPAdmin have an option to generate the actual CREATE TABLE statements for each table. If so, that would be helpful.
I could thank you a thousand times and it wouldn't express how grateful I am for your help.

You are welcome. But this a lot of fun :)
0
 
Elizabeth2Author Commented:
Hi _aqx_,,

Great answers. Thank you. Yes, I live in "workable," but would love to move toward "right."

Yes, I agree. The VIN numbers should be UNIQUE because there will never be two cars with the same VIN number.


Concerning STYLE table:
Oh, I see now.
I was thinking that I was just using the word "Category" instead of the word "Style" interchangebly (because on the example site Category is used as the type of search or filter), but now I realize those two concepts are not the same as I thought. Category is like a Sedan, Coupe, or SUV, whereas Style is 4door, 2door, etc...I will create that new table now, remove the SHM_CarStyle column and add StyleIDFK column.

Yes, I also agree, for now I will not pursue the UNIQUE and FK constraints, so that I don't get too far over my head.
Concerning TYPE for mileage and price. I don't have a NUMERIC option and don't yet know how to Alter these tables within PHP MyAdmin other than using the drop-down list provided in this tool, so I'm going to go with INT. Hopefully, that will work for mileage.

None of the prices that I see on the example site are using a decimal point, so I'm assuming that is not needed so I would think that INT would work too for this column, but will it work if I'm calculating PriceRange? Does the TYPE need to be DECIMAL for that table to function as intended?

I'm sorry to be so dumb, and pepper you with so many questions, but I have rarely had anyone to ask these questions to, it's like going to school without a teacher. Having a teacher is sooooo much better! I've got tons of books and refer to them constantly, but sometimes I don't know what questions to ask. You have taught me a lot, and I'm very grateful.

Thank you,
elizabeth
0
 
Elizabeth2Author Commented:
Hi _aqx_,

Yes, I believe I can get these tables exported. Please see attached. Is this what you need?

Also, I have to leave for lunch (I'm 3 hours late), but will return in a hour, if that's ok. If you become unavailable, no problem, I totally understand and will wait until you have a chance to post again.

Good to know your helping me is enjoyable, I love this stuff and if I could, I'd do nothing else!

Thank you!!
elizabeth ;-)
-- --------------------------------------------------------

-- 
-- Table structure for table `SHM_Used_Cars`
-- 

CREATE TABLE IF NOT EXISTS `SHM_Used_Cars` (
  `SHM_UsedCarsID` int(5) unsigned NOT NULL auto_increment,
  `SHM_PhotoName` varchar(255) default NULL,
  `SHM_CarStyle` varchar(100) default NULL,
  `SHM_CarEngine` varchar(100) default NULL,
  `SHM_CarTransmission` varchar(100) default NULL,
  `SHM_CarExteriorColor` varchar(100) default NULL,
  `SHM_CarInterior` varchar(150) default NULL,
  `SHM_CarStockNum` varchar(150) default NULL,
  `SHM_CarMileage` int(30) default NULL,
  `SHM_CarPrice` int(30) default NULL,
  `SHM_CarSpecialPrice` int(30) default NULL,
  `SHM_CarComments` varchar(3500) default NULL,
  `SHM_CarVINNum` varchar(50) default NULL,
  `SHM_CarYear` int(25) default NULL,
  `MakeIDFK` int(4) unsigned NOT NULL,
  `ModelIDFK` int(4) unsigned NOT NULL,
  `CategoryIDFK` int(4) unsigned NOT NULL,
  `SHM_Certified` varchar(10) default NULL,
  PRIMARY KEY  (`SHM_UsedCarsID`),
  UNIQUE KEY `SHM_CarVINNum` (`SHM_CarVINNum`),
  KEY `ModelIDFK` (`ModelIDFK`,`CategoryIDFK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=28 ;

-- 
-- Dumping data for table `SHM_Used_Cars`
-- 

INSERT INTO `SHM_Used_Cars` (`SHM_UsedCarsID`, `SHM_PhotoName`, `SHM_CarStyle`, `SHM_CarEngine`, `SHM_CarTransmission`, `SHM_CarExteriorColor`, `SHM_CarInterior`, `SHM_CarStockNum`, `SHM_CarMileage`, `SHM_CarPrice`, `SHM_CarSpecialPrice`, `SHM_CarComments`, `SHM_CarVINNum`, `SHM_CarYear`, `MakeIDFK`, `ModelIDFK`, `CategoryIDFK`, `SHM_Certified`) VALUES 
(1, '2010eclipse_gs.png', '4dr Car', '5.7 L', 'Automatic', 'Brilliant Black Pearl', 'Dark Slate Gray Leather', '17264', 8, 0, 0, 'THE OWNERS DEMO! MUST SEE TERESA OR DONNA FOR INTERNET PRICING! THIS IS A ALL OPTIONS 300C! AWD HEMI NAVIGATION! SATELITE DVD! MOONROOF! LEATHER! SO MUCH CAR FOR THE MONEY! CHROME WHEELS! BEST COLORS BLACK ON BLACK! Quality vehicles at real prices. There is only one Company Name. No one can beat our vehicle or warranty.Keep in mind the car that youre looking at today and deciding to buy tomorrow is the same car some one looked at yesterday, and is buying today. Finance with Company Name to take advantage of this great deal. Taxes, tags, and fees are additional. Call to set up a appointment, ask for Representative ext. (xxx)-xxx-xxxx!', '2C3LK63T39H6044002', 1994, 4, 6, 3, ''),
(7, '2010lancer_gts.png', 'gdfg', 'dgdf', 'transmission', 'gd', 'gdgfdg', 'dgd', 0, 0, 0, 'dgdfg', 'dgdg22', 1999, 2, 14, 4, ''),
(23, '2010lancer_gts.png', 'sds', 'sdfsdf', 'sdfsdf', 'sdfsd', 'sdfsdf', 'sdfsd', 0, 0, 0, 'qwe', 'fghfgh', 2005, 3, 19, 4, ''),
(24, '2010lancer-sportback_gts.png', 'ds', 'sd', 'sdfsd', 'sdf', 'sdfsd', 'fsdf', 0, 0, 0, 'sfsd', '3333', 2002, 5, 20, 3, ''),
(25, '2010lancer_gts.png', 'style', 'fasf', 'sfsf', 'sdf', 'sdf', 'sdf', 0, 0, 0, 'sdf', 'sdf', 2008, 3, 2, 1, ''),
(27, '2010lancer_gts.png', 'asas', 'asdasd', 'asd', 'sd', 'sasd', 'dadsa', 0, 0, 0, 'sdasd', 'asda', 1992, 2, 15, 5, '');




-- --------------------------------------------------------

-- 
-- Table structure for table `SHM_PriceRange`
-- 

CREATE TABLE IF NOT EXISTS `SHM_PriceRange` (
  `PriceRangeID` tinyint(4) unsigned NOT NULL auto_increment,
  `RangeMin` int(16) default NULL,
  `RangeMax` int(16) default NULL,
  PRIMARY KEY  (`PriceRangeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- 
-- Dumping data for table `SHM_PriceRange`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `SHM_Model`
-- 

CREATE TABLE IF NOT EXISTS `SHM_Model` (
  `ModelID` tinyint(4) unsigned NOT NULL auto_increment,
  `ModelName` varchar(50) default NULL,
  `MakeIDFK` tinyint(4) unsigned default NULL,
  PRIMARY KEY  (`ModelID`),
  KEY `MakeIDFK` (`MakeIDFK`),
  KEY `ModelName` (`ModelName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

-- 
-- Dumping data for table `SHM_Model`
-- 

INSERT INTO `SHM_Model` (`ModelID`, `ModelName`, `MakeIDFK`) VALUES 
(1, 'TL', 7),
(2, 'X5-Series', 1),
(3, 'Cobalt', 6),
(4, 'Tahoe', 6),
(5, 'TrailBlazer', 6),
(6, '300-Series', 4),
(7, 'Aspen', 4),
(8, 'Crossfire', 4),
(9, 'Pacifica', 4),
(10, 'PT Cruiser', 4),
(11, 'Town & Country', 4),
(12, 'Town & Country LWB', 4),
(13, 'Escape', 2),
(14, 'Explorer', 2),
(15, 'F-150', 2),
(16, 'Fusion', 2),
(17, 'Mustang', 2),
(18, 'Super Duty F-350 SRW', 2),
(19, 'Yukon XL Denali', 3),
(20, 'Camry', 5),
(21, 'Camry Solara', 5);


-- --------------------------------------------------------

-- 
-- Table structure for table `SHM_Make`
-- 

CREATE TABLE IF NOT EXISTS `SHM_Make` (
  `MakeID` tinyint(4) unsigned NOT NULL auto_increment,
  `MakeName` varchar(50) NOT NULL,
  PRIMARY KEY  (`MakeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

-- 
-- Dumping data for table `SHM_Make`
-- 

INSERT INTO `SHM_Make` (`MakeID`, `MakeName`) VALUES 
(1, 'BMW'),
(2, 'Ford'),
(3, 'GMC'),
(4, 'Chrysler'),
(5, 'Toyota'),
(6, 'Chevrolet'),
(7, 'Acura');



-- --------------------------------------------------------

-- 
-- Table structure for table `SHM_Category`
-- 

CREATE TABLE IF NOT EXISTS `SHM_Category` (
  `SHM_CategoryID` tinyint(5) unsigned NOT NULL auto_increment,
  `SHM_CategoryName` varchar(50) character set utf8 default NULL,
  PRIMARY KEY  (`SHM_CategoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

-- 
-- Dumping data for table `SHM_Category`
-- 

INSERT INTO `SHM_Category` (`SHM_CategoryID`, `SHM_CategoryName`) VALUES 
(1, 'Certified'),
(2, 'Coupes'),
(3, 'Sedans'),
(4, 'SUVs'),
(5, 'Trucks'),
(6, 'Under $10,000'),
(7, '$10,000 - $20,000'),
(8, '$20,000 and Up');

Open in new window

0
 
_agx_Commented:
Hi Elizabeth,

Can you try running this script (either from cfquery or phpMyAdmin)?  It creates a table named "TESTTABLE".

I just want to verify the types work for you on your version. (BTW - Which version of MySQL?)

<cfquery name="q" datasource="#dsn#">
CREATE TABLE IF NOT EXISTS `TESTTABLE` (
  `UsedCarsID` int(5) unsigned NOT NULL auto_increment,
  `PhotoName` varchar(255) NULL,
  `StyleID` int(5) unsigned NOT NULL,
  `Engine` varchar(100) NULL,
  `Transmission` varchar(100) NULL,
  `ExteriorColor` varchar(100) NULL,
  `Interior` varchar(150) NULL,
  `StockNum` varchar(150) NULL,
  `Mileage` int(30) NULL,
  `Price` int(30) NULL,
  `SpecialPrice` int(30) NULL,
  `Comments` varchar(3500) NULL,
  `VINNum` varchar(50) NULL,
  `CarYear` int(25) NULL,
  `ModelIDFK` int(4) unsigned NOT NULL,
  `IsCertified` BIT NULL,
  PRIMARY KEY  (`UsedCarsID`),
  UNIQUE KEY `UK_SHM_Used_Cars_VINNum` (`VINNum`),
  KEY `ModelIDFK` (`ModelIDFK`,`CategoryIDFK`)
) ENGINE=InnoDB CHARSET=utf8 AUTO_INCREMENT=1 ;
</cfquery>


0
 
Elizabeth2Author Commented:
I'm pretty sure it's version MySQL version 5.

Yes, I'm trying now.

elizabeth
0
 
Elizabeth2Author Commented:
Hi _aqx_,

I ran the query, and received this error:

Error Executing Database Query.  
Key column 'CategoryIDFK' doesn't exist in table

Did I run it correctly?
elizabeth
0
 
_agx_Commented:
Sorry, my bad.  Try this:


<cfquery name="q" datasource="#dsn#">
CREATE TABLE IF NOT EXISTS `TESTTABLE` (
 `UsedCarsID` int(5) unsigned NOT NULL auto_increment,
 `PhotoName` varchar(255) NULL,
 `StyleID` int(5) unsigned NOT NULL,
 `Engine` varchar(100) NULL,
 `Transmission` varchar(100) NULL,
 `ExteriorColor` varchar(100) NULL,
 `Interior` varchar(150) NULL,
 `StockNum` varchar(150) NULL,
 `Mileage` int(30) NULL,
 `Price` int(30) NULL,
 `SpecialPrice` int(30) NULL,
 `Comments` varchar(3500) NULL,
 `VINNum` varchar(50) NULL,
 `CarYear` int(25) NULL,
 `ModelIDFK` int(4) unsigned NOT NULL,
 `IsCertified` BIT NULL,
 PRIMARY KEY  (`UsedCarsID`),
 UNIQUE KEY `UK_SHM_Used_Cars_VINNum` (`VINNum`)
) ENGINE=InnoDB CHARSET=utf8 AUTO_INCREMENT=1 ;
</cfquery>
0
 
Elizabeth2Author Commented:
Yes, that worked! Yea! I now have the new table.
elizabeth ;-)
0
 
_agx_Commented:
Cool! You can drop it though :) It's just a test table.  Give me a few minutes to work up what *I* might use for the base model.  Then you can review it and see what you think.
0
 
_agx_Commented:
... Also, can you run a script of multiple statements via phpMyAdmin? Like

         INSERT INTO Table ....
         INSERT INTO Table ....
         INSERT INTO Table ....

(I don't use it that often)
0
 
Elizabeth2Author Commented:
Ok, dropped. You know that I will go with what ever you suggest. I would love to learn to do it the "right" way instead of just what I may or may not get to work.

elizabeth
0
 
Elizabeth2Author Commented:
I"m not sure. I can IMPORT a text file (.txt) to create a table, so it seems that it would work the same for an INSERT command, but I've never tried that. Should recreate the test table to try it out?

elizabeth
0
 
Elizabeth2Author Commented:
Unfortunately, I've got to call it a day. My life is not my own. I'm not sure what time zone you are in. Here is is 7 PM EST, and other responsibilites are calling. I'll be back at work in the morning at 9 AM to hopefully continue.

Have a great evening.
Thank you so much.
elizabeth :-)
0
 
_agx_Commented:
PT. I am a bit behind you in time zones.  Since you're leaving, I will post it later. Then and you can play around with it in the morning :) Have a great night!
0
 
_agx_Commented:
I have attached a script for creating the modified tables (via cfquery just in case), and a few sample queries. We can get to the rest on another thread, once you're comfortable with the db model.  (Note, you will have to drop the existing tables before running the create script.)

The only thing about the tables that was questionable was the "nullability" of some of the CAR table columns. For example, if you omitted all of the nullable values in a single record (price, mileage, exterior, interior, etc..) the record wouldn't contain very much information about the car.  So you might want to think about whether any of those columns should be mandatory.  But that is a simple change.

The one table you may have questions about is SHM_Category. It is a different idea I had after you mentioned you can't go with the separate category options in the interface.  It's getting late, so I'll explain it tomorrow. See what you make of it first. But dont' worry about it too much. If you don't feel comfortable with it, you can always keep the simpler structure and do a little hard coding. Not my favorite, but not the end of world either ;-)

I will check back tomorrow to see what you think of the tables.
1-createAndPopulateTables.txt
0
 
_agx_Commented:
Here is the second file
2-sampleQueries.txt
0
 
Elizabeth2Author Commented:
Hi _aqx_,
Just an FYI...
We had an ice storm here in Atlanta overnight. I was up most of the night helping a family member who was stranded alongside an icy road with a hill he couldn't climb due to black ice. One woman was killed close to where he was, slid off the road, hit a tree. There was another 29-car pile-up near the airport. Crazy drivers in Atlanta when we get ice. I was trying to find him a warm place to stay overnight since he was about 40 miles away. I ended up sleeping in this morning, and very late to work, now I'm playing catch-up.

I have downloaded the queries, thank you very much. I have another project with a tight deadline for the rest of today, but will be available tomorrow. Do you respond on the weekends? If not, no problem, just asking. I can continue over the weekend if you are available, but whenever you are available is perfectly fine with me, just as you can.

Gratefully,
elizabeth ;-)
0
 
_agx_Commented:
Hi Elizabeth,

That sounds pretty severe.  But I am glad you and your relative came through everything okay.

Yes,  I often stop in once or twice on the w/e's  (not always). Though my responses are a _lot_ more hit or miss than during the week.  So though you will probably see me around anyway,  Monday is a definite ;-)





0
 
_agx_Commented:
.... but now my queries and display pages are using those (column) names
Ugh!  I didn't notice the last part of that statement until just now.  I changed some of the column names in my script to so the naming conventions were consistent.  Not realizing you were already using them in existing code.  Sorry, knee-jerk reaction :/  I can upload another version with the original names if you need it.  Just let me know.
0
 
_agx_Commented:
RE:   I had no idea these types had anything to do with "number of records." I was under the impression that they had to do with number of characters or number of place values for a number...

Only because it's being used for an auto_increment column. Each numeric type has limits to what numbers it can hold. For example, a tinyint (unsigned) can hold any number between 0 and 255. When used for an auto_increment column, those limits become linked to the maximum number of records allowed. As soon as MySQL tries to increment the ID value beyond 255, an error will occur because tinyint doesn't allow numbers > 255.  So effectively, the range of the numeric data type determines the max. number of records you can store in your table.  If you're curious about the min/max ranges take a quick look at the very first table listed here
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
0
 
Elizabeth2Author Commented:
Hi _aqx_,

Oh, I see. So would smallint be the better choice? Thais is one of these golden nuggets of information I would have never known without your help. Thank you so much!

I will check in with you tomorrow, Monday, to ask what the next step should be. Because we've changed the database, my code no longer works of course, so I'm starting from scratch as far as the search select list is concerned. I've seen examples that use combinations of CF and JavaScript, and/or AJAX and was wondering if it can be completedly done using ColdFusion?

Thank you,
elizabeth
0
 
Elizabeth2Author Commented:
Whoops! I just saw your other post about the column names. I guess the answer depends on how you suggest we proceed with the code. The code I had, only worked for one select list search for Make. I could do a find and replace in my code, but actually, my code was searching the old "one" large table that had Make, Model, and Category within the one and only table. My code actually broke when we created the new tables, and I deleted the original Make, Model, & Category columns within that one large table.

I actually thougth the reason you changed the column names in the new tables was because, as you had mentioned previously, there was no reason to make the columns have that prefix of SHM_ before each column name, (Table name yes, column name, no) so I thought we were just cleaning up my mess, so to speak ;-), which is fine, and was probably needed.

At this point, I can reconstruct my original queries, and I still have that example of the JOIN you provided, but now with so many tables, I will probably need your help wrapping my brain around the JOINs. I know there are different kinds of JOINs, used for different reasons, and that is where I get lost. I've done a lot of reading on the subject in days past, but haven't lately, so I'm kind of lost at this point. I learn best when I can see an example and follow the logic, then I can reproduce that login on other queries.

What do you suggest would be the next step?

Thank you,
elizabeth
0
 
_agx_Commented:
Hi Elizabeth,

Yes, for that table "smallint" would definitely be a better choice. I chose "int" , but as I said that is probably major overkill ;-) So either is fine.

Yes, my changing of the column names was intended to clean-up the code.  (Instinctive reaction whenever I see code/sql in the wild... ;-) But then I read that comment, and wasn't sure how much code you had written already.  If possible, my recommendation would be to go with the new column names. But if you'd written a lot of code, I wanted to leave the choice up to you.

So, if you're okay with the columns, you could either start with the Admin section (administering the tables) or the make/model search page.  I know you posted a response on the other thread. So I'll check that next to see where you are now.



0
 
_agx_Commented:
I will probably need your help wrapping my brain around the JOINs.
No problem.  This is the fun part ;-)  Once you get the hang of them, you'll see regular JOIN's are pretty simple.  OUTER JOIN's have a few nuances. But the concept's the same.


0
 
Elizabeth2Author Commented:
_AQX_ is absolutely an awesome Expert, with thorough comments/answers, and extremely helpful directions. Most importantly, I appreciate his willingness to help me "learn" the solution, for which, I am very grateful.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 23
  • 23
Tackle projects and never again get stuck behind a technical roadblock.
Join Now