When to use new table mySql

Question

When should one create a new table in a database and use new ids? Example in a database for tickets for concerts, should I create a new table for each concert then delete it afterwards?

Keeping all the tickets in one database with unique ID's for each does not sound like a scalable solution.

This is a theory question rather than a practical coding question.

Answer 1

Here is a sample example of how you could do this in a relational pattern:

SCHEMA:

Table: USER

+---------------+-------------------+---------------+
|   UserID      |   First Name      |   Last Name   |
+---------------+-------------------+---------------+
|       1       |       John        |       Doe     |
|---------------|-------------------|---------------|
|       2       |       Jane        |       Doe     |
+---------------+-------------------+---------------+

Table: CONCERT

+---------------+-------------------+-----------+---------------+
|   ConcertID   |   Concert Name    |   Artist  |       Date    |
+---------------+-------------------+-----------+---------------+
|       1       |   Rock Concert    |   Rob     |   Jan-1-2014  |
|---------------|-------------------|-----------|---------------|
|       2       |   Rap Concert     |   Jay     |   Feb-3-2014  |
+---------------+-------------------+-----------+---------------+

Table: TICKET

+-----------+---------------+---------------+
|   UserID  |   ConcertID   |   Quantity    |
+-----------+---------------+---------------+
|     1     |       1       |       1       |
|-----------|---------------|---------------|
|     1     |       2       |       3       |
|-----------|---------------|---------------|
|     2     |       1       |       2       |
+-----------+---------------+---------------+

Raw SQL to create above schema:

CREATE TABLE USER (
  `UserID` int unsigned primary key,
  `First Name` varchar(4) not null,
  `Last Name` varchar(3) not null
) engine=innodb charset=utf8;

INSERT INTO USER
    (`UserID`, `First Name`, `Last Name`)
VALUES
    (1, 'John', 'Doe'),
    (2, 'Jane', 'Doe')
;



CREATE TABLE CONCERT (
  `ConcertID` int unsigned primary key,
  `Concert Name` varchar(12) not null,
  `Artist` varchar(3) not null,
  `Date` datetime not null
) engine=innodb charset=utf8;

INSERT INTO CONCERT
    (`ConcertID`, `Concert Name`, `Artist`, `Date`)
VALUES
    (1, 'Rock Concert', 'Rob', '2014-01-01 00:00:00'),
    (2, 'Rap Concert', 'Jay', '2014-02-03 00:00:00')
;



CREATE TABLE TICKET (
  `Ticket No` int unsigned primary key auto_increment,
  `UserID` int unsigned, 
  `ConcertID` int unsigned, 
  Foreign Key(`UserID`) references `USER`(`UserID`),
  Foreign Key(`ConcertID`) references `CONCERT`(`ConcertID`)
) engine=innodb charset=utf8;

INSERT INTO TICKET
    (`Ticket No`, `UserID`, `ConcertID`)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 2, 1)
;

You need to break apart your tables in such a way that you have one-to-one or one-to-many relationships without having a many-to-many relationship. By using a simple three table setup as described above, one user can purchase multiple tickets to multiple concerts. However, because of the itermediary table TICKET, we are never left with a many-to-many relationship. This also allows us to maintain referential integrity by tying the UserID in the TICKET table to the UserID in the USER table. In addtion, the ConcertID in the TICKET table to the ConcertID in the CONCERT table.

Finally, we are able to generate some simple queries to pull back information we need by joining the tables together. I've included two sample queries below:

SAMPLE QUERY #1 - Retrieve all tickets of a particular user by first and last name

SELECT
`conc`.`Concert Name`,
`conc`.`Artist`,
`conc`.`Date`,
`tick`.`Ticket No`
FROM `CONCERT` AS `conc`
INNER JOIN `TICKET` AS `tick` ON `conc`.`ConcertID` = `tick`.`ConcertID`
INNER JOIN `USER` AS `user` ON `tick`.`UserID` = `user`.`UserID`
WHERE `user`.`First Name` = "John" AND `user`.`Last Name` = "Doe";

Result:

+--------------------+--------------+---------------------------------+-----------------+
|    Concert Name    |    Artist    |             Date                |    Ticket No    |
+--------------------+--------------+---------------------------------+-----------------+
|    Rock Concert    |    Rob       |  January, 01 2014 00:00:00+0000 |        1        |
|--------------------|--------------|---------------------------------|-----------------|
|    Rap Concert     |    Jay       | February, 03 2014 00:00:00+0000 |        2        |
+--------------------+--------------+---------------------------------+-----------------+

SAMPLE QUERY #2 - Retrieve total number of tickets for a given concert by artist name

SELECT
COUNT(`tick`.`Ticket No`)
FROM `TICKET` as `tick`
INNER JOIN `CONCERT` AS `conc` ON `tick`.`ConcertID` = `conc`.`ConcertID`
WHERE `conc`.`Artist` = "Rob";

Result:

+--------------------+
|    Ticket Count    |
+--------------------+
|        2           |
+--------------------+

As an extra note, because we are using foreign key constraints, we have indexes defined on the particular columns. This helps SQL better manage and scale the queries/data leading to continued performance even with large numbers (millions) of rows.

Hope this helps. From here, you should be able to develop a lot of different queries to pull back all the information you desire. An exact working demo of the above code can be found below:

DEMO: sqlfiddle.com