Database Enquiry

[gothwalk]

A question for the more technically minded among ye. I'm going to be doing some work on a database of local information for inranelagh.com, and I'm think about how to structure it. At present, I'll want to flag entries in the database with various notes, and also attach tags to them, in the short-established Web2.0 style. Since there'll be an arbitrary number of possible tags for a given entry, and an occasionally changing number of flags, what's the best way to manage this?

Possibilities in my mind is to have one field, "flags", containing a comma separated list of words, and the same for tags. Another possibility is to have a field per flag, and let them be 1 or 0 - although I can't see that working for the tags. And finally, there's the possibility of a table co-relating flags/tags and entries separately.

I might just use tags overall, but I'd like to distinguish between the ones I set (flags) and the ones anyone else sets.

And a separate question - does anyone know how easy it is to get at phpBB's user storage, and if that login could be used elsewhere on the site, for comments, feedback, submissions, and the like?

I'm in an odd place on this; I know the stuff that can be done, I work with it every day, but in many ways, I haven't the slightest idea how to go about it anymore without developer support.

Comments

[sharikkamur.livejournal.com]

No no no no no! :)

Sorry - one field with a comma seperated list or a fixed number of fields for individual flags are really bad ideas. The recommended way of doing this is to have a new table which contains two fields - the primary key for the entry, and the tag. If an entry has four tags it has four records in the new table. If it has forty flags it has forty records in the new table.

Normalisation, that's what it is. :)

[theferrett.livejournal.com]

Heavily seconded.

Also, PhpBB's login functions are pretty easy to access, but PhpBB is fairly insecure. I wouldn't trust it for anything of significance. Then again, I have a hand-rolled login system I use that works fairly well, so it depends on your time.

[mollydot.livejournal.com]

Thirded.

[smarriveurr.livejournal.com]

Er... Fourthed. My DBMS prof would've tanned my hide for it. Relation table good, expandable, easily maintained and understood. CSV field messy messy messy dirty unclean inefficient bad touch. Integer with essentially bit-wise flags is expandable up to a point (ie log2(MaxInt)), but requires more work in the long run to keep clean, and locks you down once you start choosing values, whereas the table can just be cleaned out of whatever's obsolete.

So, yeah, just create new tables for such things. You'll thank yourself later.

[sharikkamur.livejournal.com]

If you want me to have a quick look at your design I'll be happy to help.

[kshandr.livejournal.com]

IrishGaming.Com piggybacks on phpBB's user table.

The one you want is phpbb_users (assuming you have the default db text extensions in place.) It's a nice, flat table with pretty much all you need in one place. When I used it I added a flag to the table which I needed (it's the bit that tags whether or not that user has anything in the scenario bank) and then I read values out of it if required.

As far as your tags go, I'd be inclined to have a table of tags and a table which relates tags to entries. To my mind it makes the whole thing easily searchable when you come to searching by tag 'cos you'll get a list of entries with tag X and then list them. As opposed to trawling through a field for a LIKE "tagname" and displaying based on that. That also will start to stink and degrade if you're searching for more than one tag. Nope - Table of tags and table of relations is my vote.

[niallm.livejournal.com]

southbull does it this way, in pseudo-SQL:

CREATE TABLE TAGS (id SERIAL, tagname VARCHAR(128));

0 Dublin
1 Landscape
2 Water
3 Animals
4 Sunset

(and so on)

CREATE TABLE TAGMAPPING (id SERIAL, tag FOREIGN KEY TAGS id, image FOREIGN KEY IMAGE id);

0 0 1
1 0 2
2 0 4

CREATE TABLE IMAGE (id SERIAL, name VARCHAR(128), tags FOREIGN KEY TAGMAPPING id);

0 'Dublin Mountains at sunset' 0

In other words, keep tags and images in separate tables and map them together with a separate tupling table.

[harmfulguy.livejournal.com]

All of this is based on the assumption that you're using a SQLish relational database, but here's how I'd do it:

First, I'll assume your base table looks something like this:

Notes go into another table referencing the base record.

If you really want to normalize your tags, you need two additional tables. First, the tags themselves have their own table, in an attempt to keep them as consistent as possible. Then you need another table to cross-reference the tags back to the original record.

Then it's easy to query all notes for a given record:
SELECT * FROM RecordNotes WHERE record_id = @record_id
Or all the records tagged with your flags:
SELECT Records.* FROM Records
INNER JOIN RecordTagXref ON Records.record_id = RecordTagXref.record_id
INNER JOIN Tags ON RecordTagXref.tag_id = Tags.tag_id
WHERE Tags.is_flag = True
...or whatever else you need.

[harmfulguy.livejournal.com]

Looking back at your original post, I think I had a separation between the concepts of "notes" and "flags/tags" that you didn't intend. Anyway, this should give you a couple different approaches to try.