-2

I am creating a database table that will store a single number 1 thru 16 in 2 fields (there will be many rows of data).

I'm trying to avoid duplicating a lot of data and make my dbase most efficient. Can I create a separate table with 1 column that has the numbers 1 thru 16 inserted then use this column as a foreign key to the first table ?

  • 1
    Can you tell is what the numbers represent? The correct answer depends on whether they identify entity or just an integer field. – kiwiron Aug 19 '18 at 5:51
  • There isn't enough information here to provide a reasonable answer to your question, other than what Christophe attempted to post below which can be summarized in one word: normalization. Why do you need numbers 1 through 16 inserted into two fields? What problem does that solve? – Robert Harvey Aug 19 '18 at 16:30
  • Like @kiwiron, I wonder if you're really just storing integers in a table. If so, just store the integers directly in the columns. If you're using those integers to refer to something else, you should state that in the question. At that point, you may be in foreign-key territory. – Jacob Robbins Aug 19 '18 at 21:07
1

The problem

I understand from your question that you store relations between two entities (identified by a number between 1 and 16) and that you want to avoid redundant data.

Let's take an example to clarify the situation:

IdFrom   TownFrom    IdTo  TownTo    Transport   Duration
--------------------------------------------------------------------
1        Paris       2     London    Plane       2
1        Paris       2     London    Train       2 
1        Paris       2     Munich    Train       8 
1        Paris       2     Munich    Plane       2
2        Munich      3     Zagreb    Plane       3
....

We see here that if you know some of its field only, we could guess the others. In fact, we see that each Id is a number between 1 and 16 (or more) and corresponds always to one town.

The solution

The solution is exactly what you are thinking of: using two tables and a foreign key relationship. You would then have two tables which are together much slimmer:

So for example, in your logic you have a mapping between your number field (here id) and additional data (here town, but it could be a lot more):

Id   Town
------------
1    Paris
2    London
3    Munich
...

and

IdFrom   IdTo  Transport   Duration
--------------------------------------
1        2     Plane       2
1        2     Train       2 
1        2     Train       8 
1        2     Plane       2
2        3     Plane       3
....

The primary key of the first table is the Id, and it would serve as a foreign key in the second table (for columns IdFrom and IdTo)

Conclusion

This process of removing redundancy is called normalization. Using a normal form is in general a good practice for relational databases. It has some advantages:

  • Each table has data that is directly related
  • You can still have the full information, by joining the two tables (ok, with two foreign keys to the same table, it's a little bit more tricky in SQL, but fortunately StackOverflow will help you out)
  • You avoid inconsistencies (e.g. with your denormalised table you could have typos such as 1 Munich 2 London, so is 1 Paris or Munich ?)
  • You have to change data only in one place (e.g. if you'd decide that the name of the town should be stored in its original language, like München instead of Munich, you'd need to update it once and all the data still remain consistent).
  • You could let the database take care of the consistency (e.g. not suppress a town id if it's still used somewhere).
  • It's a nice answer, but I think you got the premise of the question wrong. – Robert Harvey Aug 19 '18 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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