0

For an assessment task I'm doing, an entity album has the attribute also_bought, which is a self-referential attribute. However, this one attribute has multiple entries for any one album - as the also_bought recommendations are rarely only one recommendation - and thus, is a bit of a question mark when it comes to normalisation. I'm not sure whether it passes 1NF or not.

To be clear, the entire entity's set is

Album(album_id, title, playtime, genre, release_date, price, also_bought)

philipxy
  • 14,867
  • 6
  • 39
  • 83
Leafsw0rd
  • 157
  • 8
  • What definition of "1NF" do you mean? (See link in my answer.) What does "multiple entries" mean? That also_bought values are lists of album_ids? What exactly are the types of your columns? – philipxy Jul 31 '17 at 12:59

2 Answers2

0

"Also bought" items should be stored in a separate table, something like.

AlsoBought (table)
  album_id
  also_bought_album_id

Then configure foreign keys from both columns to reference Album.album_id.

JSR
  • 6,106
  • 2
  • 20
  • 24
0

You mean that Album is a "self-referencing table" because it has a FK (foreign key) from one column list to another in the same table? (A FK constraint holds when subrow values for a column list must appear elsewhere.) If you mean that the type of also_bought is a list of album_ids, there is no FK from the former to the latter, because values for the former (lists of ids) are not values for the latter (ids). There's a constraint that is reminding you of a FK.

Anyway, normalization is done to one table, and doesn't depend on FKs.

But any time you are "normalizing to 1NF" eliminating "non-atomic columns" you have to start by deciding what your "table" "columns" contain. If you decide a cell for a column in a row contains "many values" then you don't have a relational table and you have to come up with one. The easiest way is to assume a set-valued column to get a relation and then follow the standard rules for elimination of too-complex column types.

philipxy
  • 14,867
  • 6
  • 39
  • 83