2

I have a table:

  CREATE TABLE pupils (
     id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
     name TEXT, surname TEXT, address TEXT, score INTEGER
  );

where score is a percentage.

Is it more efficient to have the integer value in there or a foreign key to a table that is just a table like this?

  CREATE TABLE score (percentage INTEGER PRIMARY KEY NOT NULL);

Obviously the table would be populated with 0-100.

My thoughts were if you have 1000's of pupils that keeping it as an integer column would mean queries were faster, but more space used and using the foreign key would mean less space used, but slower queries.

Not sure if this is correct?

So I thought I would throw it out there!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ne0
  • 2,688
  • 3
  • 35
  • 49

2 Answers2

2

If you use the FOREIGN KEY to protect the score value in the pupils table, you still need to have a score column in pupils. You can't have a FOREIGN KEY relationship without columns in both tables.

The only advantage of the FOREIGN KEY is that it would let you restrict the values allowed in the score column to integers between 1 and 100. The same thing can be done, however, with a CHECK constraint on the column, without the need for the extra table.

If you use the FOREIGN KEY, your DDL would look like this:

CREATE TABLE pupils (
 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
 name TEXT, surname TEXT, address TEXT, score INTEGER REFERENCES score(percentage)
);

Note that the score column is still there.

In this case the FOREIGN KEY approach adds an whole table and two indexes that have to be maintained. Much more efficient to use one table and a CHECK constraint.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • You are right, I was aware that the column would still exist. Though I was unsure if one way was far better then the other. My thoughts were to normalise it to reduce repetitive data being added to the database, though I was unsure if doing this would create inefficiency in querying. Are saying it would be inefficient? – Ne0 Nov 06 '12 at 16:52
  • This is not an issue of normalization. The *same value* will be stored in the `pupils` table regardless of whether you have the FOREIGN KEY table. There is no advantage in this case to using the FOREIGN KEY table. – Larry Lustig Nov 06 '12 at 16:55
  • I see. I was unsure of how `FOREIGN KEYS` are stored. I couldn't see anything in the SQLite documentation that described it, i thought it could be either a pointer or the actual data. (In reality the pointer may be bigger (more bits) then then integer its pointing to, but like i say i have no idea which is stored.) – Ne0 Nov 07 '12 at 09:08
1

There is no need for normalizing the value of score.

It's a value, it belongs to the first table.

That would create an unneeded link (to an integer), that wouldn't really impact performance per say, but it would affect readability.

Yan Brunet
  • 4,727
  • 2
  • 25
  • 35
  • What is your opinion on space though? If the database is huge, with millions of students, taking 10's of exams. Would't using a score table reduced the size dramatically? – Ne0 Nov 06 '12 at 16:36
  • You will still use the space to provide the ID of the foreign key that relate to the value in the Score table. – Yan Brunet Nov 06 '12 at 16:38