Entries Tagged 'Databases' ↓

Anyone here familiar with DB_DataObject (If you don’t know it, a general answer would still be very helpful)? I’m trying to figure out how to handle a n:n (or 1:n in some cases) relationship.

I have 3 tables:

Filter

+-----------+
| Field     |
+-----------+
| filter_id |
| name      |
| type      |
+-----------+

Feed

+---------------+
| Field         |
+---------------+
| id            |
| title         |
| htmlUrl       |
| description   |
| categorise    |
| etag          |
| last_modified |
| status        |
+---------------+

And feed_filter

+-----------+
| Field     |
+-----------+
| feed_id   |
| filter_id |
+-----------+

The tasks I need to perform:

* Get all filters attached to a feed
* Get all feeds attached to a filter
* Get all filters attached to all feeds (so when printing a list of feeds I can show the filters)

What is the proper way to handle these tasks? Do I have to do 2 queries for each of these, or is there a better way?

This morning I knocked up a quick and dirty “blogmarks”:http://simon.incutio.com/archive/2003/11/24/blogmarks script. Being the control-freak I am I wanted to add categorisation to the blogmarks; but instead of just adding the blogmark (from now on referred to as BM) to one category I wanted multiple categories to be possible.

First thought was the usual ‘1 table for blogmarks; 1 for categories; and one for the blogmark/category relationships’. Usual way, if IMO a bit overcomplicated for such a simple job.

Then I though of “PostgreSQL’s”:http://www.postgresql.org “array support”:http://www.postgresql.org/docs/current/interactive/arrays.html. This would mean only 2 tables were necessary and would group the data more appropriately. Except… something was stirring in the back of my mind. Didn’t this undo some of the normalisation?

From the reading I’ve done PostgreSQL’s arrays aren’t supported as arrays directly in PHP. So it wouldn’t make any difference to using one column in the database table and storing the category ID’s in a comma separated format (which I believe is similar to what PostgreSQL does internally). Except that arrays are much easier to update than changing 17 to 20 in @1,5,17,3,2@.

A bit of googling didn’t turn up any useful information as to whether arrays in a database are considered harmful or not - so what do you guys think?

Oh, and the blogmarks will appear on this site when I can decide where to put them (and how to format them so it doesn’t look like a rip-off of Simon’s site)