mysqlsqllistxampp

Which is the best way to insert a list inside a cell in SQL?


I am creating a simple social network in which I use xampp (and so, mysql and php).
I am facing a dilemma regarding which is the best way to include a list of objects as an element of a row.

I'll explain it better: in my project, which contains students and professors as unique users, every one of them has a list of projects they are partecipating into, and every project has a list of industries/firms/companies who are financing or simply observing the project; every company has its own profile.

My dilemma regards which is the best way to represent this architecture:


Solution

  • What you are asking about, without knowing, is the concept of normalization. It's worth looking into. A simple design for your system could look like this:

    people(id, name, ... type)
    projects(id, name, ... )
    people_projects(project_id, people_id)
    companies(id, name, ...)
    company_projects(company_id, project_id)

    Each professor or student is a single row in the people table. Each project is a single row in the projects table, each person ON a project, is a single row in the people_projects table. same for companies.

    You then access your information via joining the tables together, eg to get all students on the project with name 'Project X', you would do a simple inner join:

    select *
      from projects p
        inner join project_people pp
          on p.id = pp.project_id
        inner join people pl
          on pp.people_id = pl.id
      where p.name = 'Project X'
        and pl.type = 'student';
    

    Simple, robust database schema, and simple data access.