user6227447 user6227447 - 4 months ago 7
MySQL Question

What is the best practice for storing multi-layered data in mySQL?

In the past I've only used HTML, CSS and JavaScript for website development; but haven't really touched PHP or MySQL so am self-teaching them now.

I want to start as I mean to go on so was wondering on the general opinion of what is best to structure the following type of data in MySQL.

If I had various entries about people with the following fields; ID, Name, DateOfBirth, Telephone Numbers. If I were storing this information as a variable in JavaScript for each entry I'd have the 4 values as an array with the Telephone Numbers entry being an array within that array of all the Telephone Numbers. Example:

["355","John","12/12/2016",["07123456789","07987654321"]]


In MySQL obviously there isn't an 'array' data type; so I was wondering how you personally would store data in a similar format to the above in MySQL; the options I see from my current understanding of MySQL are the following.


  1. Have 4 columns for the 4 values, have an string of the array of Telephone Numbers which can then be split into an array once retrieved from the database.

  2. Have 4 columns initially and then have the database automatically add a new column every time a new Telephone Numbers is added to an entry when there isn't room for it (so if the most Telephone Numbers any entry had was 4 you'd have ID,Name,DateOfBirth,Telephone1,Telephone2,Telephone3,Telephone4 as the columns).

  3. Have a separate table in the database for telephone numbers with one entry per number stored with the columns ID and Telephone number, ID referring to the ID in the original table. Then when I need the Telephone numbers of an entry I run a query that searches for all entries with that ID and returns them.



I'm sure there must be other methods; so if the best method isn't one of the above please let me know.

Kind Regards.

Answer

Option #3 would be the standard normalized relational database way to store this.

Your tables might look like (for example):

create table Person (
  `ID` int auto_increment primary key, 
  `Name` varchar(255), 
  `DateOfBirth` date);

create table Telephone (
  `ID` int, 
  `Number` varchar(20), 
  foreign key (`ID`) references Person(`ID`) on delete cascade);