Variable number of columns in SQL database

Question

I am trying to create a SQL database to be used with PHP to generate dynamic webpages. Each page is about one episode (of a TV show) and includes some number of links to places to watch it. However, not every episode has the same number of links.

I'm very new to SQL and databases in general, but it seems like I could just set some sort of limit (say, 10) for the number of links and skip over null columns for episodes with less than 10 links. However, this seems inefficient and not the correct way to do this.

Alternatively, I did find this post which suggests using a many-to-many relationship of binding tables together. This seems a lot more complicated, but also more correct and efficient.

Which is the best choice in this situation? How would I implement it?

Bigwater · Accepted Answer · 2016-01-18 02:50:30Z

1

It is a one to many relationship not a many to many.

1 Series can have many episodes but one episode cannot have many series.

2 tables, Episodes & Series

Episodes table has all current attributes but has series_ID attribute as well. (This is the primary key in the series table).

Series has the series ID as the primary key and all other attributes.

You can make these foreign keys if your server supports it. (Extra feature)

When it comes to selecting it is quite simple.

Select * From Episodes,Series WHERE Episode.SeriesID = 'x'

'x' is equal to the series ID you currently want to find the episodes for.

answered Jan 18, 2016 at 2:50

Bigwater

2232 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bigwater Over a year ago

I deleted previous comment, as this solution is simpler and better. It will work if done correctly. Please click the tick next to my answer if this helps you. Cheers

Bigwater Over a year ago

Side Note: You would add as many episodes as required with the same series ID. This would would result in many rows being returned with the select statement.

Collectives™ on Stack Overflow

Variable number of columns in SQL database

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related