Need help with MySQL database tables

[Kieran Hinckley]

Hi i'm currently in the process of creating my first website. The aim is for the user to select a series of options from a drop down box such as the apple device they own and the iOS version that is running on it the compatible jailbreaks will be displayed along with a download link and a link to any relevant website.

What i have done so far can be found at the link below http://www.jailbreakfinder.com/

However i'm having difficulty in how to do the MySQL database. At first i thought of one table for the devices and then tables for the different iOS versions on each device but i'm thinking there might be a simpler way?

Also if anyone has any suggestions on how i can make the site look better, let me know!

 

[FabienO]

Why not just one table with

id | device | ios | link

SELECT DISTINCT device FROM link_table
Giving you a list of devices.

SELECT DISTINCT ios FROM link_table WHERE device = 'device name'
List of iOS's for that device.

SELECT link FROM link_table WHERE device = 'device name' AND ios = 'iOS'
Gives you the download link

 

[uniQ]

One table is bad, may work for now but think ahead.

Start with 3 tables:
device
ios
Then link those using a foreign key in another table

You may also want to consider things like baseband, Operating System, Network(s) as well or in the future etc and having separate tables to manage them will make it easier to manage.

 

[FabienO]

One table is fine, the guy isn't making a million row database.

May as well advise him not to store anything but ints in the tables then have another table with the characters each each represents, or a global array of names. It's a small job and only needs a simple table.

 

[Kieran Hinckley]

I gotta admit, one table sounds much easier to set up. This kind of idea could potentially be expanded to a "ROM finder" for android devices. Searching for android ROM's depending on manufacturer, device, android version, hboot, themed/non themed which will probably require more than one table.

I have now made the table and am currently in the process of inserting data into it. As each device is compatible with more than one version of iOS, how can that be incorporated in a single table?

Or is this a situation where more than one table (devices, iOS versions and jailbreaks) would be more appropiate through the use of one to many relationships linking each device to the iOS versions it supports?

 

[uniQ]

the guy isn't making a million row database.

/facepalm

If you need me to quantify then try saying that to your boss or your first client.

You should always consider how the project will scale in the future and plan ahead.

 

[FabienO]

/facepalm

If you need me to quantify then try saying that to your boss or your first client.

You should always consider how the project will scale in the future and plan ahead.

The advice was based partially on the ability of the person asking and the direct needs of the site. I have no idea what his long-term goals are. But the advice I gave was simple and does the job. Maybe not the most scalable way to lay out a database, but suggesting something the OP might not be able to do isn't the best advice either.

Or is this a situation where more than one table (devices, iOS versions and jailbreaks) would be more appropiate through the use of one to many relationships linking each device to the iOS versions it supports?

It depends on how far the rabbit hole goes. If there's going to end up a lot of redundant data inside of the single table, you could try something like uniQ suggested.

Device Table
ID | Device

iOs Table
ID | ios

Link Table
ID | device_id | ios_id | link

The data in each table would be something like

Device Table
1 | Device 1
2 | Device 2
3 | Device 3

iOs Table
1 | iOs 1
2 | iOs 2
3 | iOs 3

Link Table
1 | Device 1 ID | iOs 1 ID | Link 1
2 | Device 1 ID | iOs 2 ID | Link 1
3 | Device 2 ID | iOs 2 ID | Link 2
4 | Device 2 ID | iOs 2 ID | Link 3
5 | Device 3 ID | iOs 2 ID | Link 1
6 | Device 3 ID | iOs 2 ID | Link 3

End query would be something like

SELECT link FROM link_table WHERE device = 'Device 1' AND 'ios' = 'iOs 1';

 

[Kieran Hinckley]

That table design looks great, thanks for that!

I'm just in the process of making the tables and inserting data into them, got the Device table done so far.

In terms of the website itself, is there anything you would change about the look of it to make it look better?

 

[FabienO]

The design is fairly plain. I would recommend browsing template sites that fit the needs of your site. Then use them as inspiration for a new design.

Template Monster
Open Source Web Design
and so on...

Don't fit the data to the design, fit the design to the data . Look at what your site requires. One menu, two, etc etc... then look for similar designs that match those needs.


Sometimes some sites simply require a small clean, non-flashy design. example: http://www.query-ip.com/

 

[Addicted]

Why bother with MySQL? Use some funkeh Ajax and get some html guides written, and load a html depending on the dropdown's value

 

[Kieran Hinckley]

Why bother with MySQL? Use some funkeh Ajax and get some html guides written, and load a html depending on the dropdown's value

I will be using ajax, just not as you described it. I'm using it to dynamically load the mySQL database without reloading the page.

Quite a lot of progress has been made, i've got some drop down boxes now!
http://jailbreakfinder.com/

 

[FabienO]

Mux the two. Have the next drop down options be based on the preceding ones selection.

IE I select "iPhone 4", it changes the iOs list to only those selectable for iPhone 4.

 

[Kieran Hinckley]

Mux the two. Have the next drop down options be based on the preceding ones selection.

IE I select "iPhone 4", it changes the iOs list to only those selectable for iPhone 4.

That's next on the "To Do" list. Is it hard to do?

 

[FabienO]

Not hard to do, maybe a little annoying to understand at first.

The Function

function makeSublist(parent,child,isSubselectOptional,childVal)
{
	$("body").append("<select style='display:none' id='"+parent+child+"'></select>");
	$('#'+parent+child).html($("#"+child+" option"));

		var parentValue = $('#'+parent).attr('value');
		$('#'+child).html($("#"+parent+child+" .sub_"+parentValue).clone());

	childVal = (typeof childVal == "undefined")? "" : childVal ;
	$("#"+child+' option[value="'+ childVal +'"]').attr('selected','selected');

	$('#'+parent).change( 
		function()
		{
			var parentValue = $('#'+parent).attr('value');
			$('#'+child).html($("#"+parent+child+" .sub_"+parentValue).clone());
			if(isSubselectOptional) $('#'+child).prepend("<option value='none'> -- Select -- </option>");
			$('#'+child).trigger("change");
                        $('#'+child).focus();
		}
	);
}

On the page

<script type="text/javascript">
$(document).ready(function()
{
    makeSublist('drop_down_one','drop_down_two', false, '1');
});
</script>

<?php $dropDownOne = $database->RunQuery("SELECT * FROM devices"); ?>

<select id="drop_down_one" name="drop_down_one">
  <?php foreach ($dropDownOne as $d) { ?>
    <option value="<?php echo $d->id; ?>"><?php echo $d->name; ?></option>
  <?php } ?>
</select>

<select id="drop_down_two" name="drop_down_two">
  <?php foreach ($dropDownTwo as $d) { ?>
    <?php include('dropdowntwo.php'); ?>
  <?php } ?>
</select>

dropdowntwo.php

<?php $devices = $database->RunQuery("SELECT * FROM devices"); ?>
<?php foreach ($devices as $device) { ?>
  <?php if($device->name == 'iPhone4') { ?>
    <option value="iOs1">iOs1</option>
    <option value="iOs2">iOs2</option>
  <?php } ?>
  <?php if($device->name == 'iPhone3') { ?>
    <option value="iOs2">iOs2</option>
    <option value="iOs3">iOs3</option>
    <option value="iOs4">iOs4</option>
  <?php } ?>
<?php } ?>

Something like that. Use link table to dynamically make dropdowntwo.php

 

[Kieran Hinckley]

Thanks, i've got that done now.

To Do List
1.Find jailbreaks for all of the devices and insert them into the database
2. Make all of the drop down boxes line up
3. Submit the site to google and other search engines. Might use godaddys "Search engine visibility" tool.
4. Think of some more ways to improve the layout. You will notice a change in the header, background and download logo if you go on the site now.

 

[Kieran Hinckley]

While entering data into the database i encountered a bit of a problem.
On some devices such as the iPad 2, there is no jailbreaks available for certain iOS versions. Is it possible to have a message appear in the results box stating something like "There are no jailbreaks available" if no jailbreaks are available?

Would this work?

else {
print "<script type=text/javascript">;
print "alert("No jailbreaks are currently available for your selected iOS version")";
print "</script>";