Add database "sub-table" per element

Question

I'm not sure if a "sub-table" is the proper term for it, so let me explain a bit better.

I'm setting up a website which contains multiple items, now I've created 2 separate tables in my MySQL database: general and platforms.

My goal now is to split the data of each item into these 2 tables, which works fine so far, but my problem now is the following:

The platforms table has the following structure:

ID
Name
URL

I want to keep track of each item by their ID, so the ID for item #1 should be equal in all tables.

Now, if I have say 3 different platforms for item #1, I'll add every element in the platforms table, but their ID's don't match.

And if I have multiple items, each with multiple platforms it will start to look really messy.

Is it possible to have a table that looks like this?

ID
    Name
    URL

Hopefully the images clarify it more, basically; I want to have a table that groups together multiple elements.

Is this possible or would I have to do it by assigning a secondary non auto-incrementing ID to each item and manually group the platforms together in PHP?

Googled on `foreign keys?` Thought you were asking about an xref style join table... Think though you just need to read up some on FKs. See; http://stackoverflow.com/questions/1730837/can-someone-explain-mysql-foreign-keys and http://stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys — ficuscr, Aug 10 '15 at 18:48
@ficuscr It looks like FKs could work, I'm reading http://www.sitepoint.com/mysql-foreign-keys-quicker-database-development/ right now. — Snakybo, Aug 10 '15 at 18:55

spencer7593 · Accepted Answer · 2015-08-10T19:01:01.213

Looks like you have a one-to-many relationship. Generically, that means

a row in general can be related to zero, one or more rows in platforms.

a row in platforms is related to exactly one row in general.

To implement this design, store the id value from the general table as a foreign key in the platforms table.

id
general_id    -- foreign key references id in general table
name
url

Rows in the two tables are related by virtue of a common value.

  id  general_id  name        url
 ---  ----------  ---------  --------------------------
  77           1  Platform1  http://item1.com/platform1
  78           1  Platform2  http://item1.com/platform2
  79           1  Platform3  http://item1.com/platform3

To have the database enforce referential integrity, you would need to use a storage engine that supports that (e.g. InnoDB), and you can declare a constraint

ALTER TABLE `platforms` ADD 
 `general_id`  INT NOT NULL COMMENT 'fk ref general.id' AFTER `id`;

(The datatype of the general_id columns must exactly match the datatype of the id column in the general table.)

Before you can enforce the constraint, the values in the new general_id column will have to match a value in the referenced column.

To define the constraint:

ALTER TABLE `platforms`
  ADD CONSTRAINT FK_platforms_general
  FOREIGN KEY (`general_id`) REFERENCES `general`(`id`)

Add database "sub-table" per element

1 Answers1