Normalization/Schema for classifying cross-browser bugs based on rendering engines and browser versions?

Question

I'm trying to decide on a schema for storing cross-browser bugs across all the rendering engines.

Here's what I had in mind:

browser_engines table:

id  name  version
1   gecko 1.5
2   gecko 1.7
3   gecko 1.8
4   gecko 1.9.0
5   gecko 1.9.1

browser_versions table:

id  name      version  engine_id
1   firefox   3.0      4
2   firefox   3.5      5

browser_bugs table:

id  name    description   engine_id
1   ff bug                4

So if I pulled the first bug, it would map to gecko 1.9.0, so the html view would render the browser affected as Firefox 3.0.

Question 1.1: Does this schema make sense? Is it normalized enough?

Question 1.2: What data type should the version column be?

Answer 1

I would create an engine table (i.e. one row for gecko, and a FK from browser_engine to engine), similarly a browser table. This'll lower storage requirements and speed up queries. I'd also consider storing the version in major / minor / revision fields to make querying for 'all bugs in revision 2.5 or previous' easier ("10.0" < "2.5" - so strings not suitable for such a query).

Answer 2

Question 1.1: Does this schema make sense? Is it normalized enough?

Hey! That's two questions. ;-)

This schema assumes some things, such as:

• Every browser version has only one browser engine.
• Every bug in a given browser engine is guaranteed to affect every browser that uses that engine.

If either of these is not always guaranteed to be true, you might need some many-to-many intersection tables.

Question 1.2: What data type should the version column be?

I would go with VARCHAR to account for "4.0 release candidate 1" and such. I'd allow at least length of 30.