Hi,
Apologies to all those who read this via mailing.database.mysql, for the repost. I didn't realise that there wasn't two way synchronisation between the mailing list, and the news group. I'm looking for some advice on database structure. Without unnecessary details, I have a database that deals, in part, with 'matches' each of which progresses through a series of statuses. I need to store when the match progressed to each of these statuses. At present, I have two tables, matches and matchstatuses: matches: matchid (PK) etc... matchstatus: matchstatusid (PK) matchid status (ENUM) datechanged etc... This works fine, except that it's somewhat messy to query, and I assume potentially slow, as I have to resort to using HAVING ... an awful lot. What I was considering is using a SET field, status, in the matches table, whose values coincided with the status ENUM. I would just update this at the same time as any inserts into matchstatuses. I'm using transactions, and the code is reasonably simple, so maintaining consistency isn't an issue. This would certainly simplify a lot of my queries, as most don't require the date information. My main concern is with style. This seems a somewhat messy solution to the problem, and I can well imagine such redundancy being frowned upon. I did also consider just adding a set of fields to the match table, each nullable, and corresponding to a given status. This however seemed wrong as: 1) A lot of the matches will not go through a lot of the statuses, leading to wasted storage of 4 bytes per status per record. 2) I'd rather update the ENUM / SET definitions than add additional fields if new statuses need to be added. 3) ENUMs and SETs are sort of normalised, whereas having a field per status clearly isn't. I'd be very grateful for any thoughts on whether using an ENUM and SET in the above mentioned way is considered very bad form, as well as whether using a field per status is better or worse. I'd also be very grateful of any suggestions as to an better approach to this. Many thanks, Chris Key
