0

Hi,

I've been evaluating FogBugz 8 online for a little while and so far I've been impressed. Today I decided to use the "Download Database" feature (which is a pretty nice feature). I grabbed the SQL Sever version and loaded it up to see what was inside.

My first stop with a new database is usually the database diagram node. If there are any diagrams it really helps (me) with quickly understanding a database. Since this was basically a backup of a production database I wasn't really surprised that there were none in there, so I decided to build my own using the online schema documentation.

I added the project table and all related tables (based on the schema documentation). I was surprised to find that there were no foreign keys actually defined in the database. I proceeded to add them myself and found a couple odd things:

  1. The TokenAssociation table has the ixProject field set to smallint instead of int like its parent (project) table. So until I altered this I was unable to define the relationship.

  2. I tried to define a relationship between the Project and ACL tables, but the constraint failed because there was a row in the ACL table with a ixProject ID value that did not exist in the project table (value was zero)

These are both problems that would not be present if official constraints had been defined. The first one seems a little odd to me, and should probably be fixed? The second could be a design decision for maintaining history or something. I don't know.

Honestly, I'm a little leery of databases with no relationships defined because it's so easy to introduce errors like I've outlined above. Is this so you can support both MySQL and SQL Server maybe? Is it for performance considerations? I'm curious (obviously).

Oh, also, I was going to submit this as a bug report, but I couldn't find where to do that...is this basically the correct place for this?

Thanks, Andrew

flag
Yes, this is a totally fine place to submit this kind of thing. I like that future customers can search for "foreign keys" and get this discussion. The first issue is likely a bug and I'll file it. The second is by design. And you hit the nail on the head. We rely minimally on native database functionality (no stored procedures, triggers, constraints) because we need to support multiple databases. It's not indicative of sloppy design. The constraints are all in the code layer. – Rich Armstrong♦ Oct 22 2010 at 20:02

1 Answer

1

FogBugz does not enforce or use foreign key constraints. For the most part, FogBugz tries to deal with these things in code so there's one place to go when things aren't working right.

I'd say the most likely consequence of adding constraints to your database will be to cause FogBugz to fail at some point. We realize that this makes the database harder to diagram, but we have strong (column) naming conventions to make up for it. (Apologies for the inconsistent pluralization of table names... sigh.)

link|flag
Thanks for the reply. I wasn't planning on re-implementing it with the constraints in place, I was just trying to understand the product a little more as part of my evaluation. I was pretty sure that there was some reason it was the way it was, but I figured I'd ask why. The fact that you let evaluation customers download a full copy of the database shows how confident you are in your product (as does this open discussion). Thanks for the quick reply and answer! -Andrew – Andrew Oct 21 2010 at 16:13

Your Answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.