jakebur01 Posted July 23, 2017 Share Posted July 23, 2017 (edited) Say you have the following: 1 Sample1a 2 Sample1b 3 Sample1c 4 Sample1d 5 Sample2 6 Sample3 7 Sample3a 8 Sample3b I need to be able to display the data in the following format: View all Sample1 Models Sample1a Sample1b Sample1c Sample2 View all Sample3 Models Sample3 Sample3a Sample3b So, in a select box or a list of links I need to put a "View all" of this model if it has multiple versions of the model. A, B, C, D, etc. I didn't know the best way to do this. Should I add an additional column and put Sample1 in it on the Sample1a, Sample1b, etc. columns. Or should I create another table and use the id's from the first table and add Sample1 next to them. The next issue is how to do the query to where I can stick a View all of this model every time you come to a model that has multiple versions. Edited July 23, 2017 by jakebur01 Quote Link to comment https://forums.phpfreaks.com/topic/304390-best-way-to-set-up-tables/ Share on other sites More sharing options...
Jacques1 Posted July 23, 2017 Share Posted July 23, 2017 Put the rendering details aside and focus on the database layout. You need one table for the versions and one table for the models. If a model has no variants, you store a single version. With this approach, there's nothing special about models with multiple versions, so you don't need any fancy queries. The "View All" becomes a minor rendering feature which you implement by simply checking the number of versions within your application. Quote Link to comment https://forums.phpfreaks.com/topic/304390-best-way-to-set-up-tables/#findComment-1548759 Share on other sites More sharing options...
jakebur01 Posted July 23, 2017 Author Share Posted July 23, 2017 (edited) Yes but there will be cases when I will have multiple versions of a model that are not a series of like versions. My current setup is something like this: Type id model make category multiple 1 F150a Ford Truck F150 2 F150b Ford Truck F150 3 F150c Ford Truck F150 4 Viper1 Jetski Boat 5 Viper2 Jetski Boat 6 Viper3 Jetski Boat Viper3 7 Viper3a Jetski Boat Viper3 8 Loco2 Heavy8 Train Loco2 9 Loco2a Heavy8 Train Loco2 10 Loco4 Heavy8 Train In the case above, F150, Viper3, and Loco2 are the only ones that have multiple versions of the same model. Edited July 23, 2017 by jakebur01 Quote Link to comment https://forums.phpfreaks.com/topic/304390-best-way-to-set-up-tables/#findComment-1548761 Share on other sites More sharing options...
jakebur01 Posted July 23, 2017 Author Share Posted July 23, 2017 How about something like this?: Type id model make category multiple view_all 0 View all F150 Ford Truck F150 Y 1 F150a Ford Truck F150 N 2 F150b Ford Truck F150 N 3 F150c Ford Truck F150 N 4 Viper1 Jetski Boat N 5 Viper2 Jetski Boat N 6 View all Viper3 Jetski Boat Viper3 Y 7 Viper3 Jetski Boat Viper3 N 8 Viper3a Jetski Boat Viper3 N 9 View all Loco2 Train Loco2 Y 10 Loco2 Heavy8 Train Loco2 N 11 Loco2a Heavy8 Train Loco2 N 12 Loco4 Heavy8 Train N Quote Link to comment https://forums.phpfreaks.com/topic/304390-best-way-to-set-up-tables/#findComment-1548763 Share on other sites More sharing options...
Jacques1 Posted July 23, 2017 Share Posted July 23, 2017 (edited) I have no idea what you're doing there and why you're struggling with the very simple model I suggested. If what you've posted is supposed to be a database table, then you need to learn the basics of SQL like types and normalization. Edited July 23, 2017 by Jacques1 Quote Link to comment https://forums.phpfreaks.com/topic/304390-best-way-to-set-up-tables/#findComment-1548765 Share on other sites More sharing options...
jakebur01 Posted July 23, 2017 Author Share Posted July 23, 2017 I've read some of your posts and I feel like you come across as insulting to some of the members who are only trying to learn. You are very smart in the area of PHP and MySQL. I realize some are only looking for someone to write their script instead of learn. But, other people are not likely to come back if they feel stupid every time they post code and ask a question. Quote Link to comment https://forums.phpfreaks.com/topic/304390-best-way-to-set-up-tables/#findComment-1548772 Share on other sites More sharing options...
Jacques1 Posted July 23, 2017 Share Posted July 23, 2017 I like people who are trying to learn. What I find irritating is to see somebody run into the exact opposite direction after I just explained an approach. If you need clarification – no problem. You're also welcome to criticize my ideas and come up with better ones. But you actually suggested the two-table layout yourself, I confirmed it, then you suddenly decided it's the wrong approach and went to something which doesn't even look like a database table. It also seems to me that a lot of programmers (definitely not just you) skip the basics, jump straight to complex applications and then expect to get the job done with a few ad-hoc fixes. As a result, an enormous amount of time is spent on going through the same basics over and over again instead of actually discussing the application – which would be a lot more interesting for everyone. Quote Link to comment https://forums.phpfreaks.com/topic/304390-best-way-to-set-up-tables/#findComment-1548781 Share on other sites More sharing options...
jakebur01 Posted July 23, 2017 Author Share Posted July 23, 2017 What are a few sources for learning the basics? Quote Link to comment https://forums.phpfreaks.com/topic/304390-best-way-to-set-up-tables/#findComment-1548791 Share on other sites More sharing options...
Jacques1 Posted July 23, 2017 Share Posted July 23, 2017 If you're talking specifically about databases, the Wikipedia article is actually a good start, because it provides an overview, many examples and lots of keywords for further research. The MariaDB developers have also published several tutorials which explain the basics in a very practical manner. If you prefer books, I can recommend the introductions from O'Reilly. And then there are countless online tutorials which usually aren't too bad. For learning SQL, it doesn't really matter that much what you read. The important part is to read at all, think critically and try to get a feeling for what is right and what is wrong. As a concrete example: In your above one-table layout, you keep repeating the information that the F-150 is a truck from Ford over and over again. This should make you doubt the whole thing. Isn't this a waste of space? What if you end up with conflicting information? What if you have to update or delete all the data? Those questions more or less naturally lead to the concept of normalization. But the most important insight is that the relational model works in a very specific way and is designed to store data unambiguously and efficiently. It's not like an Excel spreadsheet where you store anything you want in any way you want as long as human readers can make sense of it. Again a concrete example: You want actual booleans, not informal, unrestricted strings like “Y”/“N”. If the application code has bugs (which happens) or Jimmy touches the database, you quickly end up with empty strings, nonsensical strings and plenty of variations: “Yes”/“No”, “y”/“n”, “0”/“1”, ... Quote Link to comment https://forums.phpfreaks.com/topic/304390-best-way-to-set-up-tables/#findComment-1548793 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.