Is it bad practice to store multiple ids into one table column

Question

I'm working on a project where the database has a few tables that contain a type_id field that stores id's from multiple tables

for instance:

id | table_type | table id
==============================
1    ADDRESS      1
2    ADDRESS      2
3    CITY         1
4    CITY         2
4    ADDRESS      3
5    COUNTRY      1

the table_id field holds either an id from the Addresses table, or the Cities table, or the Countries table

I'm just wondering if this is good design. or should i avoid this whenever possible?

This table is used to grab all locations that a user has entered.

http://stackoverflow.com/questions/922184/why-can-you-not-have-a-foreign-key-in-a-polymorphic-association/922341#922341 — Damir Sudarevic, Aug 19 '11 at 18:35
**YES!** it violates even the **first normal form** of database design! Column values ought to be **atomic** - one single value — marc_s, Aug 19 '11 at 18:53

score 3 · Accepted Answer · answered Aug 19 '11 at 18:41

The answer is:

It Depends.

If the example table you gave was named Location and you're using it to achieve type inheritance, where Address, City, and Country are specific types of Location, then this design can work. In this case, your primary key will be in the Location table, and each of the other tables will have a foreign key to Location. If that's not how you're using it, then this is not a properly normalized database design.

score 0 · Answer 2 · answered Aug 19 '11 at 18:35

This seems like a confusing design. If you tables Address, City, and Country each have their own id field, then tables which reference them should use a column name like Address_id, City_id, and Country_id - respectively.

Your current design is trying to be too generic. It's bound to cause you trouble later on.

score 0 · Answer 3 · answered Aug 19 '11 at 18:37

0

Look, tTif you can make keys with the values you have.

in this case you could make a combination of table_type and table id the primary/unique key.

Though if the table_type can be only one of a few values maybe save the types as an enum?

answered Aug 19 '11 at 18:37

sg3s

9,411
3
36
52

1

I would try to avoid starting a reply with "Look". Not trying to be a post police here but it starts the reply off on a rather aggressive tone whether that's the intention of it or not. – Dylan Hayes Aug 19 '11 at 18:41

Is it bad practice to store multiple ids into one table column

3 Answers3