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:
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.