Table without PK

I've a table of items, with a rack in determinate time period, exactly one week, and i need make the rank table of the request week, ex:

Items
-----
id 
name

And:

Item_in_rank
------------
item_id -> Items.id
year
week
rank

if i do:

SELECT
    r.rank,
    i.*
FROM
    Item_in_rank AS r 
INNER JOIN
    Items AS i
       ON r.item_id = i.id
WHERE
    week = 4 AND year = 1986
ORDER BY
    r.rank ASC

I gets a ranking:

rank id  name
1-    4  jhon
2-   76  jorge
3-   21  myriam
4-   92  bety

Obiouly i can have a once item in various weeks/years开发者_运维技巧, ever i get a list. I don't need a pk in this model, but, is correctly it?

If i define as pk (week, year), i dont can have:

week year item rank
  1  1980   41    1
  1  1980   32    2
etc..

if i define as pk (week, year, item) i would need to create an index for (week, year), and the pk would be useless.

It this correctly? or i'm wrong?

---

You always need a PK especially when you think you don't. The purpose of a PK is to ensure you can uniquely identify a record at all times. This is not optional for the database to perform correctly (try to delete dups when you don't have a PK and if you don't have one there is a 100% chance you will have dups) and does not mean you might not need other indexes as well.

---

if i define as pk (week, year, item) i would need to create an index for (week, year), and the pk would be useless.

Adding a column to an index makes the index larger (bad thing) but it doesn't make it less useful. MySQL can use an index on (col1, col2) to search on col1. So by all means, change your primary key to (week, year, item).

As an example of an index that wouldn't work: if your primary key is (col1, col2), then MySQL can't use it to search for col2. That would be like using a phonebook sorted on lastname, firstname to search for a first name.

It this correctly? or i'm wrong?

You're wrong! :)

---

In many database (I'm not sure about mysql) the PK is more like a constraint on the table. In other words, the table is the PK. The data is stored in PK order but is not significantly larger because of the PK.

Try adding the PK and see if the data storage size increases. Add the secondary index (which might require a PK first) to help your query perform properly.

Don't worrry too much about creating a useless PK. Test it yourself to see if it is worth creating.