Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools

Posted by Andrew Barber
Andrew Barber
3,865 Points

mySQL Database

Hi All,

I am a 18 year old student who is currently in need of a lot of advice. My main aim is to create a mobile application which will send and receive data from a webserver.

I have been told that in order to meet my aims, I need to do the following:

  • Create a database using SQLYog with all the data
  • Use Apache for the Server
  • Xcode for the iPhone application

This is the order of tasks I was told to do it in:

  • Create database -add all the possible queries -create the server
  • create the iphone application

I could really do with some advice on how I can do a number of things e.g. create the server, organizing my data into tables and creating the relationships.

I have completed most of the SQL tutorials and just need some advice.

11 Answers

John W
John W
21,558 Points

Well, there are a lot of steps to this so I'm not sure Skype would be the best option. In the best case the chat would devolve into a hold-your-hands step-by-step walkthrough and two months later you wouldn't remember how your app works.

What I can do now is expand on your steps, and you can start building it and ask any additional questions on the forum if you can't find answers by searching online.

At this point I'm assuming that you are already comfortable with creating a table and executing queries including insert and select

The next step for you would be to create an API that your app can access. An easy way would be to create a PHP interface that queries your mySQL db with the appropriate query based on the GET parameters provided. To learn this, take a look at this project. PHP is not your only option, but it is very easily to learn and works great on an Apache server.

Ideally your API would only simply require you to type in a URL with some GET parameters trailing at the end, and it will spit out something e.g. a JSON string that your iOS app can easily parse/ingest. Here is an example from the Google Maps Geocoding API:

http://maps.googleapis.com/maps/api/geocode/json?address=Sunnyvale,CA&sensor=true

An API like this is called RESTful. Long story short, you can easily type this into your browser and see its output before proceeding to working on your app and its behavior is quite predictable so it's easy to test. Try changing Sunnyvale,CA in the above URL to any address of your choice and see how the output changes.

After you have your API ready and tested, you can access it via your iOS app using NSURLSession or AFNetworking.

Andrew Barber
Andrew Barber
3,865 Points

Hi John,

Thank you for your reply.

Currently my issue is trying to work out how I should form or organise the table, rows,columns within my database.

I have three tables:

Nike, WESC, Reebok

All tables have the following columns:

shirts,jackets,coats,trousers,jeans,sweatshirts.

I need to add 10 products to each category, with the ID, Name and price.

The most important thing is to add the sizes XS, S,M,L,XL.

The other issue I have is that all sizes need to be recorded with the following:

collar, chest, waist, hips, arms, leg AND needs to be in both CM and Inches.

Should I be creating tables for EVERYTHING or should is there something else I should be doing?

Just struggling to understand the structure or how I will add everything I mentioned into one database.

Thanks.

John W
John W
21,558 Points
John W
John W
21,558 Points

Ahhh, the good ole' schema design problem. Unfortunately for you, the way you structured the problem is the source of the difficulty.

Taking from the OOP playbook: a row in the table can be conceptualized as an instance of what the table general represents and each column represents a way to describe that instance. So think about your "Nike" table and the columns you proposed. Is each "Nike" item described simultaneously by those columns? Hence instead of having Nike, WESC, Reebok tables with shirts,jackets,etc columns, you might want to go back and think about what you are representing: clothes.

Now let's start with a table clothes. How would you describe a single piece of clothing item? You have the brand (Nike,WESC,Reebok,etc), you have the type (shirts,jackets,coats,etc), ID, name, price, size (XS, S, M, L), and more!

Separately you want to be able to look up a size based on the code e.g. 'M'. So maybe you want a size table with the different measurements as columns and maybe (just maybe) also the brand (XL from Reebok probably means something different than an XL from Nike). HOWEVER you don't need to store both CM and Inches. If you store both, every time you have to change one number you ended up needing to change both. Pick a unit, and let your app or API does the unit conversion for you.

Andrew Barber
Andrew Barber
3,865 Points

OK I'm starting to understand where I'm going wrong now. Thank you!

But the main issue still does not make sense to me. I agree that I should have a separate size table, but each product has different sizes e.g:

Nike:

Jacket 1 = Chest 110cm, Sleeve 65cm, Length 72cm Shoulder 45cm Jacket 2 = Chest 120cm, Sleeve 75cm, Length 82cm Shoulder 55cm

WESC:

Completely different sizes for each and every jacket

Then I need to have a table which has a specific range to decide what is classed as medium, large, and small.

This is what I am struggling to understand :S

John W
John W
21,558 Points

Before I give you my solution, can you propose one yourself? Spend some time to think about it first before giving up. Here are some hints/questions to think about:

  1. If you have a different size for each item regardless of the size category, i.e. each size combination is presumably unique to a single clothing item, what's the easiest way to correspond a row in the sizes table to a row in the clothes table?

  2. Is a sizes table still necessary if each item has a presumably unique/different size? This question can be argued both ways, so argue against yourself until you are comfortable with your reasoning.

  3. If the size category, i.e. S/M/L, can be re-computed by using some logics based on other existing parameters, i.e. transient, do you really need to store it? Can it be calculated at fetch time?

Andrew Barber
Andrew Barber
3,865 Points
Andrew Barber
Andrew Barber
3,865 Points

It's starting to sound like I need to have one table which just lists everything: name,id,brand,type, chest,waist.

In my eyes, it doesn't seem like an efficient way for creating the database, but then again, i'm just a amateur at all of this.

I plan to spend 20hours a day learning as much as possible but in all honesty I'm trying so hard to understand everything!

But going back to the topic, the solution I think I should be proposing is to have everything all in one.

John W
John W
21,558 Points
John W
John W
21,558 Points

Good job!

You are correct in that (in practice) you only need 1 table with everything + chest,waist,etc. The difference between the use of 1 table vs. 2 tables in this case is Second Normal Form (2NF) vs. Third Normal Form (3NF). You can find out more about it if you google the topic of "relational database normalization".

Good luck with your project.

James Barnett
James Barnett
39,199 Points
James Barnett
James Barnett
39,199 Points

John W - Rock on, this is some of the one of the best helping I've seen on the forum.

Andrew Barber
Andrew Barber
3,865 Points
Andrew Barber
Andrew Barber
3,865 Points

John W Thank you Sir!

Andrew Barber
Andrew Barber
3,865 Points
Andrew Barber
Andrew Barber
3,865 Points

John W VALUES(.......

but when I had a look at one of the websites I was looking to use, they display their sizes as a range e.g. chest s - 32-34inches, m - 36-38inches.

What would be the best way of displaying ranges within mysql?

Thanks!

Andrew Barber
Andrew Barber
3,865 Points
Andrew Barber
Andrew Barber
3,865 Points

this is a wild guess, should i be doing chest_lo, chest_hi all in that same table?

Andrew Barber
Andrew Barber
3,865 Points
Andrew Barber
Andrew Barber
3,865 Points

John W James Barnett After going back to the mySQL project on treehouse. They used two different tables in the first video Movies and Genre. After that each table was linked by the id number. I've also had other people telling me i should create relational databases. I am really confused and would really like to understand the best and easiest way to do this. Can anyone please help me to understand as I have spent the last few hours trying to understand this.

John W
John W
21,558 Points
John W
John W
21,558 Points

OK, considering what you have told me so far, your proposed solution, and other factors. My conclusion is that you are overcomplicating your 32-day project by trying to imitate too much of a real-life system.

I'm traveling right now for work, but I can Skype with you later this week (after Wednesday). What's your Skype username?

Meanwhile, go take a look at the PHP/MySQL project and start learning for your API.

Andrew Barber
Andrew Barber
3,865 Points
Andrew Barber
Andrew Barber
3,865 Points

John W Thank you!

I think after we speak, you'll start to understand what why I am going down the lines of creating a database which kinda imitates a real system. I am solely dedicated to finding the solution and completing the database with the 32 day period.

Almost completed the php/mySQL tuts.

P.S. Sorry if im a pain. :l

Andrew Barber
Andrew Barber
3,865 Points
Andrew Barber
Andrew Barber
3,865 Points

Hi John W,

Just wondering if we could have another discussion over skype if you are free. I think I have completed the database but I'm running into a few issues.