CODEWITHSUNDEEP
databasedatabase-design
vangoz
vangoz

Reputation: 546

Database design - boolean attribute or new table

Let's say I have a database table that I want to filter based on a boolean attribute (for example, "flagged" attribute). Is it better to just add a "flagged" attribute to the table, or make a new table which has foreign key to the base table? What are the pros and cons?

Upvotes: 5

Views: 2820

Answers (4)

Darwin
Darwin

Reputation: 4786

It depends. I myself prefer adding a new table when the value is not really related to the table/relation.

A good example of this is when you have table representing orders and you want to keep track of which ones has been printed. I would add new table called printed_orders with a foreign key to the order.

create table printed_orders (
  order_id int primary key references order(order_id)
);

If its been printed or not is not really part of the order but part of the system/business rules.

Upvotes: 0

MiGro
MiGro

Reputation: 1511

Just add column to the table, it is better and simpler.. then create a bitmap index what will speed up your queries if you use this column in WHERE

Upvotes: 0

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

If that is all you need1, then just add the simple field.

But, you'll have to be careful about how you index it. Unless the values are seriously skewed2, you'll end-up with an index with a horrible selectivity and clustering factor3, and you'll actually be better off doing full table scans4.

If you filter by the flag in conjunction with other fields, make a composite index, which will then have a much better chance of being decently selective.

1 I.e. you don't need additional data that somehow "describes" or "augments" each of the two possible Boolean values.

2 One value is in deep minority (the number of rows containing false is much smaller than the number of rows containing true, or vice verse), and you happen to filter on just that value.

3 The link is for Oracle, but the general principle applies to all DBMSes.

4 Which a decent DBMS will do for you automatically, even in the presence of the index. A hypothetical "stupid" DBMS would just blindly use the index and perform even worse than the full table scan.

Upvotes: 3

c0dem0nkey
c0dem0nkey

Reputation: 686

If this flag has attributes by itself or if it is reusable, better create it as another table. But if its just something to mark a row as true/false, just make a bool column(and save time and work effort)

Upvotes: 0

Related Questions