I need help with my Database architecture and exporting to Excel

jej07
jej07 used Ask the Experts™
on
I'm setting up two tables; one that contains events and one that contains event registrations. My thought is to give each event a unique key and either implode the data and insert it into one column in the registrations table (Option1 below). Or, insert each event key in a separate column of the registrations table (option2 below).

I'm not really sure which method is best; but I do know every two weeks I'll need to export the registrations to Excel. And the end user requesting the Excel document will need a separate column for each event, and will want the column headers to show the event name, not the unique key.

Using the code below, I can export the registrations table; but I can't figure out how to replace the uniquieID with the event name.

example:

events
| description    | date              | uniqueID |
| Fun Event 1   | 2012-08-16 | 52               |
| Fun Event 2   | 2012-08-17 | 53               |
| Fun Event 3   | 2012-08-18 | 54               |

Option1
registrations
| name           | registrations   |
| John Doe    | 52,54                 |
| Jane Doe    | 52,53,54           |

Option2
registrations
| name           | 52 | 53 | 54 |
| John Doe    |  y  |       |  y  |
| Jane Doe    |  y  |  y   |  y  |  

Desired export
| name           | Fun Event 1    | Fun Event 2   | Fun Event 3   |
| John Doe    |          y                |                            |             y            |
| Jane Doe    |          y                |           y               |             y            |  

$export = $DB->query("SELECT * FROM `registrations` ORDER BY `name` DESC");

$fields = mysql_num_fields($export);

    for($i = 0; $i < $fields; $i++){
        $header .= mysql_field_name($export,$i) . "\t";
    }

   
      while($row = mysql_fetch_assoc($export))
      {
                  $line = '';
                  foreach($row as $value) {
                      if ((!isset($value)) OR ($value == "")) {
                         $value = "\t";
                      } else {
                        $value = str_replace('"', '""', $value);
                         $value = '"' . $value . '"' . "\t";
                      }
                      $line .= $value;
                  }
                  $data .= trim($line)."\n";
      }
    $data = str_replace("\r","",$data);

    if ($data == "") {
        $data = "\n(0) Records Found!\n";
    }
    header("Pragma: public");
    header("Expires: 0");
    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=$fileName.xls");
    print "$header\n$data";
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Both option1 and option 2 are wrong,
the correct one is

registration
| name        | registration |
| John Doe    | 52           |
| John Doe    | 54           |
| Jane Doe    | 52           |
| Jane Doe    | 53           |
| Jane Doe    | 54           |
Any comments pl.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Thank you very much for your reply. I had a feeling my options might not be the best way to go about things. Just to help me better understand, what is the advantage to creating separate rows for each registration, rather than imploding the data?

I think the URL sent is exactly what I needed. I need to play with it a little more to see if I can get the logic figured out.
Most Valuable Expert 2011
Top Expert 2016
Commented:
You might also consider the use of a "junction table" or "intersection table" that relates the individuals to the events.  You may want to keep a lot of information about both the individuals and the events.  This kind of information would be kept in separate tables.  The intersection tables provide the relational nature of the data in the separate tables.  Hence the name "relational data base."
Dear Jej07,

To understand better, it is necessary to read relational database nomalization concept (it is  my opinion, pl ignore if I am wrong). To suit your particular case, pl go through the URL for good and bad database design,

http://www.jpmartel.com/bu12ce03.htm
Most Valuable Expert 2011
Top Expert 2016
Commented:
what is the advantage to creating separate rows for each registration
To understand the answer to this question you need to have something of a background in computer science.  In the instant case, you might get away with just imploding the row numbers into a single string and storing it, and that would be fine if that is all you ever want to do.  If you ever want to search by row number, or extend the capabilities of the data base beyond the four corners of this assignment, you're going to need to find each of those row numbers separately.  It is axiomatic in data base design that each column+row intersection contains an atomic and uniquely identifiable data element.  So when a column may have one, two or more row numbers it causes the warning bells to go off.  This is the sort of thing that a data base administrator would be able to help you with.

Many of the links here are pretty good.
http://lmgtfy.com?q=Should+I+Normalize+my+Database

If you're new to data base programming, you might enjoy this book.  It will not make you a pro, but it is very readable and has good examples that relate directly to the kinds of things you're working on in this question.
http://www.sitepoint.com/books/phpmysql4/

Author

Commented:
Wow, that's a lot to digest.  A junction table may be a better solution so I can add contact information for each registrant.  If I understand correctly, the registrations and events table would have unique rows. Then I would need a third table matching up the other two?

I haven't been able to get through all of the URLs you both referenced yet, so I'm not sure if this is addressed or not. But I'm not sure right off how I would select the values so they can be exported correctly.

BTW @ Ray_Paseur, I download the first four chapters of the phpmysql4 book and have found it very informative. I do plan on downloading the entire book after I've finished reading those chapters.

events
| description    | date              | uniqueID |
| Fun Event 1   | 2012-08-16 | 52               |
| Fun Event 2   | 2012-08-17 | 53               |
| Fun Event 3   | 2012-08-18 | 54               |

registrations
id       | name           | address              | city              |
1      | John Doe    | 101 State Street      | Chicago       |
2      | Jane Doe    | 1512 Elm Street      | New York      |

event_registrations
registration_id      | event_id      |
1            | 52            |
1            | 54            |
2            | 52            |
2            | 53            |
2            | 54            |

Author

Commented:
Thank you very much for your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial