best way to set up tables

[jakebur01]

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.

[Jacques1]

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.

[jakebur01]

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.

[jakebur01]

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

[Jacques1]

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.

[jakebur01]

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.

[Jacques1]

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.

[jakebur01]

What are a few sources for learning the basics?

[Jacques1]

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”, ...