Database design - do I need one of two database fields for this?

Question

I am putting together a schema for a database. The goal of the database is to track applications in our department. I have a repeated problem that I am trying to solve.

For example, I have an "Applications" table. I want to keep track if any application uses a database or a bug tracking system so right now I have fields in the Applications table called

Table: Applications
UsesDatabase (bit)
Database_ID (int)
UsesBugTracking (bit)
BugTracking_ID (int)

Table: Databases:
id
name

Table: BugTracking:
id
name

Should I consolidate the "uses" column with the respective ID columns so there is only one bug tracking column and only one database column in the applications table?

Any best practice here for database design?

---

NOTE: I would like to run reports like "Percent of Application that use bug tracking" (although I guess either approach could generate this data.)

Answer 1

Either solution works. However, if you think you may want to occasionally just get a list of applications which do / do not have databases / bugtracking consider that having the flag fields reduces the query by one (or two) joins.

Having the bit fields is slightly denormalized, as you have to keep two fields in sync to keep one piece of data updated, but I tend to prefer them for cases like this for the reason I gave in the prior paragraph.

Another option would be to have the field nullable, and put null in it for those entries which do not have DBs / etc, but then you run into problems with foreign key constraints.

I don't think there is any one supreme right way, just consider the tradeoffs and go with what makes sense for your application.

Answer 2

I would use 3 tables for the objects: Application, Database, and BugTracking. Then I would use 2 join tables to do 1-to-many joins: ApplicationDatabases, and ApplicationBugTracking.

The 2 join tables would have both an application_id and the id of the other table. If an application used a single database, it would have a single ApplicationDatabases record joining them together. Using this setup, an application could have 0 database (no records for this app in the ApplicationDatabases table), or many databases (multiple records for this app in the ApplicationDatabases table).

Answer 3

You could remove the "uses" fields and make the id columns nullable, and let a null value mean that it doesn't use the feature. This is a common way of representing a missing value.

Edit:
To answer your note, you can easily get that statistics like this:

select
  count(*) as TotalApplications,
  count(Database_ID) as UsesDatabase,
  count(BugTracking_ID) as UsesBugTracking
from
  Applications

Answer 4

Why not get rid of the two Use fields and simply let a NULL value in the _ID fields indicate that the record does not use that application (bug tracking or database)

Answer 5

To answer the edited question-

Yes, the fields should be combined, with NULL meaning that the application doesn't have a database (or bug tracker).

Answer 6

"Should i consolidate the "uses" column"

If I look at your problem statement, then there either is no "uses" column at all, or there are two. In either case, it is wrong of you to speak of "THE" uses column.

May I politely suggest that you learn to be PRECISE when asking questions ?

Answer 7

Yes using null in the foreign key fields should be fine - it seems superfluous to have the bit fields.

Another way of doing it (though it might be considered evil by database people ^^) is to default them to 0 and add in an ID 0 data row in both bugtrack and database tables with a name of "None"... when you do the reports, you'll have to do some more work unless you present the "None" values as they are as well with a neat percentage...