PHP: MySQL table structure

Question

I'm developing a website that has some audio courses, each course can have multiple lessons. I want to display each course in its own table containing its different lessons.

What kind of a database should I make?

Example as a static website:

<p><span class="heading1">Course 1 - Speaker 1</span> </p>
    <p><span class="date">Posted in <a href="Audio.html">TAG 1</a>, december 12, 2010</span></p> 
    <class id="text">
    <p>Info: blablabla</p>
    <p>&nbsp;</p>
    <table  border: none cellpadding="1" cellspacing="1">
      <tr>
        <th scope="col">Nr.</th>
        <th scope="col">Lesson</th>
        <th scope="col">Date </th>
        <th scope="col">Download</th>
      </tr>
      <tr>
        <td scope="row">1</td>
        <td>blablabla</td>
        <td>22/12/2010</td>
        <td><a href="">MP3</a></td>
      </tr>
      <tr>
        <td bgcolor="#D6D6D6" scope="row">2</td>
        <td>blablabla</td>
        <td>22/12/2010</td>
        <td><a href="">MP3</a></td>
      </tr>
    </table>

Course 2 - Speaker 2

    <p><span class="date">Posted in <a href="audio.html">TAG 2</a>, december 12, 2010</span></p> 
    <class id="text">
    <p>Info: blablabla</p>
    <table  border: none cellpadding="1" cellspacing="1">
      <tr>
        <th scope="col">Nr.</th>
        <th scope="col">Lesson</th>
        <th scope="col">Date</th>
        <th scope="col">Download</th>
      </tr>
      <tr>
        <td scope="row">1</td>
        <td>blablabla</td>
        <td>06/12/2010</td>
        <td><a href="01.mp3">MP3</a></td>
      </tr>
      <tr>
        <td scope="row">2</td>
        <td>blablabla</td>
        <td>13/12/2010</td>
        <td><a href="02.mp3">MP3</a></td>
      </tr>
    </table>

simshaun · Accepted Answer · 2010-12-22 17:43:33Z

2

Table: courses
id
title

Table: lessons
id
cid (course id)
title
date
file

Sample SQL:

SELECT
    lessons.*,
    courses.title AS course
FROM
    lessons
INNER JOIN
    courses
        ON
    courses.id = lessons.cid
GROUP BY
    lessons.id
ORDER BY
    lessons.date

I figure you could easily just use an auto-incrementing variable for the lesson number, as you output the table. However, if there are actually specific lesson numbers, you may want to add it as a field to the database.

You may also want to add additional order by arguments.

edited Dec 22, 2010 at 17:43

answered Dec 22, 2010 at 17:09

simshaun

21.5k1 gold badge60 silver badges73 bronze badges

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

3 Comments

webbiedave Over a year ago

Aren't lessons unique to courses? If so, rather than having a cross-reference table, you should simply have a lessons.course_id field.

simshaun Over a year ago

Ya know, I didn't think enough about it. You're right. Edited my answer to reflect it.

A3efan Over a year ago

Thanks! Another question: What kind of a table structure do I need if I wanted to expand it with different Speakers and tags. And being able to sort by Course, Speaker or Tag.

Collectives™ on Stack Overflow

PHP: MySQL table structure

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related