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
Jake Craige
4,913 PointsStoring array like data in a database?(PHP, MySQL)
I am working on a web app for work and I currently have it working in a bit of a strange way that also may not be optimal.
DB Structure that applies to this problem:
Events Table
- id
- site_number
- event
- timestamp
Problems Table
- id
- site_number
- event
- linked_events
- status
The application takes in data that is sent and stores it in the events table. It then checks this against the rules set in another table to see if it creates a problem which is then displayed to the user. If problem status is open for a specific site and has the same event, it then adds the event id to the linked_events row in the problems table. The data in linked events is stored like this
12 44 23 154 212 123
When i need to check that data I just use explode() on the spaces which works fine to separate it and do what I need to do with it. My question is if this is a good option or are there better ones. These are what I have come up with
Add a linked_to column to the events table that stores the problem id. That way I could just query events and use a WHERE statement to pick out the ones for the problem id that I need. Which may be faster than using php to explode and sort through data. Maybe not because it will not have to sort through as many records? I have a lot to learn about optimization for speed
Add a linked_events table where I will store the event id and the problem ID and can query that, and then query the other tables, but for speed, this may be slower than the other ways because it would require at least 2 queries. I'm not sure though
Leave it as is and be happy to not make any changes :)
Thanks!
7 Answers
Jake Craige
4,913 Pointsmarkdown fixed.
Randy Hoyt
Treehouse Guest TeacherThe proper way to structure the database depends on the nature of the relationship between events and problems.
- It sounds like a single event can have multiple problems; is that correct?
- It sounds like a single problem can apply to multiple events; is that correct?
If so, then Option 2 is the correct approach. You'll have three tables total:
- Events
- Problems
- Events_Problems
This third table is considered a relationship table, and it's a pretty common way to store many-to-many relationships.
You will need two queries to get the data you want. Let's say you want to list all the information about an event, including it's related problems. You'll need one query to get the event information, and you'll need one query to get the list of related problems. But those two queries are fast: a simple read from a single table for the first one, and a two-table query with an inner join for the second one.
Jake Craige
4,913 PointsFor the first part that is incorrect.
- The problems can have many events, but the event can only have 1 problem. I feel like i'm learning more towards adding the problem id column to the events table.
Let me know which one is best though.
Thanks!
Jake Craige
4,913 PointsSo I just looked up what INNER JOIN does. And it sounds awesome and super useful, I've heard it before but never used it, but it seems that would would great on what I'm trying to do. I need to brush up on my sql.. lol. I've just been querying tables and having php compare them. Ex: nested mysqli_fetch_array's.. Whoops?
I guess I need to wait for these Database Foundations videos to come out :)
Randy Hoyt
Treehouse Guest TeacherIf an event cannot have more than one problem, tean having a problem_id column in the events table is the right solution.
Here's another question: Could an event have zero problems? Or do they always have exactly one problem?
Jake Craige
4,913 PointsYes an event can have 0 problems. The problem is only generated when a set number of events are received. And it only logs the future events after that, it doesnt log the ones that led up to it being created, so those ones would not be linked
Randy Hoyt
Treehouse Guest TeacherThe first set of tutorials in Database Foundations has launched while we've been talking.
Here are some follow up thoughts.
- If you want to display information about a problem and all of its related events, you'll need two queries.
- If you want to display information about an event and its related problem, you'll either have to write either (a) two queries or (b) one query with an outer join.