2

I have something that looks like this:

if ( $_SESSION['username'] ) 
        $owner = $_SESSION['username'];

    $q = "SELECT * FROM projects where owners='$owner' ORDER BY created DESC";

The problem is that the 'owners' column may have multiple entries separated by commas. I need a way to cycle through all the entries and select any table that has $owner in the owners columns. Whats the best way to go about this?

Thomas
  • 5,030
  • 20
  • 67
  • 100
  • 1
    What do you mean with cell? You've a multivalued column? Owner column has multiple values? – santiagobasulto Oct 06 '11 at 23:05
  • 1
    You've made **wrong** design decision. Instead of storing multiple values in a *single cell* - you'd better had it moved to *separated table*. – zerkms Oct 06 '11 at 23:07
  • Sorry, when I said cell, I meant column. – Thomas Oct 06 '11 at 23:09
  • @zerkms How would moving the owners to a different table help? Wouldn't I just end up putting multiple entries in to a single column on a different table? – Thomas Oct 06 '11 at 23:12
  • @Thomas: no - you would have N lines per project according to the number of owners. So each owner would be a separate row: `1 a`, `1 b`, `1 c` <-- the sample of 3 owners of project id = 1 – zerkms Oct 06 '11 at 23:13
  • @zerkms So with this setup, I would have potentially unlimited tables on my database - is there a way to organize the tables into a group? Should I use a separate database altogether? – Thomas Oct 06 '11 at 23:22
  • 2
    @Thomas: I can't get what you mean. With this setup you'll have 1 table for projects and 1 table for owners. That's it, just 2 tables. – zerkms Oct 06 '11 at 23:25
  • 1
    See my answer to [Is storing a comma separated list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Oct 06 '11 at 23:47
  • @BillKarwin Thanks for sharing this, I now understand why this is a bad design decision, however, I am still having trouble wrapping my mind around how to structure this. Are there any other resources you could point me to? I'd google it but Im not exactly sure what I am looking for. – Thomas Oct 06 '11 at 23:57

1 Answers1

1

The proper design for a relational database is to store only one value in a given "cell" (i.e. in a given column on a given row -- but please do not use spreadsheet terminology with relational databases, you'll make Joe Celko cry. :-).

Here's an example:

CREATE TABLE Projects (
 project_id INT PRIMARY KEY,
 ...
);

CREATE TABLE Users (
 user_id INT PRIMARY KEY,
 ...
);

CREATE TABLE Project_Owners (
  project_id INT NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY (project_id, user_id),
  FOREIGN KEY (project_id) REFERENCES Projects(project_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

You insert one row to Project_Owners table for each owner of a given project. So you can easily have a project with multiple owners, and a user who works on multiple projects.

We say that Projects has a many-to-many relationship to Users.

So when you want to get projects associated with a given owner, you'd need to look up the owner in the users table, and then join that to projects via the many-to-many table:

<?php

$owner = mysql_real_escape_string($_SESSION["username"]);

$sql = "SELECT p.* FROM Projects
INNER JOIN Project_Owners o USING (project_id)
INNER JOIN Users u USING (user_id)
WHERE u.name = '$owner'";

Or if you want to simplify it, you can use integers in your session data. Then you can skip a join:

<?php

$owner = (int) $_SESSION["username"];

$sql = "SELECT p.* FROM Projects
INNER JOIN Project_Owners o USING (project_id)
WHERE o.user_id = $owner";

The concept of storing just one value in a cell is part of the basic definition of First Normal Form. Any book on SQL should cover this, even the Manga Guide to Databases.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828