4

I'm modeling a voting system which has the following entities:

  • category
  • nominee
  • phase

As the name suggest, I'll be storing categories and nominees in the respective tables. The voting will have two phases. In the first phase there'll be 8 nominees per category. The 4 most voted nominees will pass to the second (and final) phase.

So far I have this structure (simplified)

category
    id      PK
    name

nominee
    id      PK
    name

phase
    id      PK
    name

My problem is how to model the voting part. I think I have 2 options, but I'm not sure which one is better or what are the pros / cons of each:

Option 1: Having a category_nominee table with a composite 3 column primary key (I'm pretty sure the "canonical" PK here is formed by these 3 fields; not sure about performance implications; I'm using mysql)

category_nominee
    category_id         PK
    nominee_id          PK
    phase_id            PK

What I don't like about this is that to reference category_nominee from the votes table I'll have to use these 3 columns again, since I dont' have an single identifier in category_nominee. So, in the vote table I'll have to repeat the 3 columns:

vote
    id
    category_id         FK
    nominee_id          FK 
    phase_id            FK

Additionally, I'm not sure if category_id should point to category.id or to category_nominee.category_id (I'm leaning towards the latter)

Option 2: Create an autoincremented id column in category_nominee and make category_id, nominee_id and phase_id a composite unique key.

category_nominee
    id
    category_id         Unique
    nominee_id          Unique
    phase_id            Unique

vote
    id                  PK
    category_nominee_id FK

This will simplify referencing a category_nominee record and will avoid some repetition. I expect to have much more records in vote than in category_nominee. Still I'm not sure which option is more convenient.

SQL Fiddle for option 1

SQL Fiddle for option 2

compte14031879
  • 1,531
  • 14
  • 27
Juan Pablo Califano
  • 12,213
  • 5
  • 29
  • 42

1 Answers1

1

From what I learned about modeling data, option 1 is the good option. Maybe this is the reason for the existence of foreign keys. Never seen option 2.

But in your option 1, category_nominee and vote are duplicates. Implement something like this :

category
    id      PK
    name

nominee
    id      PK
    name

phase
    id      PK
    name

vote
    (category_id         FK
     nominee_id          FK 
     phase_id            FK) PK
    //others fields required or not

Nothing prevents you from renaming the (category_nominee.)category_id field, if you want unique column names in all your tables. You simply have to link this column to the origin column as a foreign key.

compte14031879
  • 1,531
  • 14
  • 27