Suppose there is a User , which has 2-3 status "pending" , "active" , "blocked" .

To preserve integrity, there is a table statuses :id, :name

Well, 2 options to set this status.

1) It seems to me that it is better to make the primary key name , refusing from id hang a foreign key on this field.

Then you can do User.set_status("active")

2) Colleagues say that this is the wrong approach. It is necessary that the id was required. This will allow faster search, about some tree indexes say.

However, during the development process, the status setting is complicated.

 status_id = find_status_by_name("active") User.set_status(status_id)

Tell me pliz than the 1st option, so much worse than the second?

With my Noob look, I see only the minuses of the second variant and the advantages of the first one.

  • one
    And what about the fact that PK imposes a restriction of uniqueness?) Read the basics ... - Nick Proskuryakov
  • To preserve integrity there is a table statuses: id,: name It is not clear exactly how you use it. And in what form is stored acc. attribute in the main User table. From the text of the question (if everything is true there) it follows that the main table stores the text of the status, and not its identifier in the reference table. Ps. I think for the sake of integrity and kosher you should read the reference about ENUM ... - Akina
  • 2
    Option 3, and fast and simple: CREATE TYPE user_status AS ENUM ('pending', 'active', 'blocked'); - Fat-Zer
  • @NickProskuryakov so no one offers to make some field not unique, if that) And without asserting the uniqueness of the name field - the database will not allow to make this field a foreign key. Everything is OK with the basics) - Nekifirus
  • @Akina yes and it is proposed to use. instead of status_id = find_status_by_name ("active") and User.set_status (status_id) do just User.set_status ("active"). And at the DB level, there is a link that checks that such a status really exists. In general, out of the situation, in another comment, he answered) - Nekifirus

1 answer 1

int lightweight line => this implies the following advantages:

  1. Index will be smaller

  2. Tables that will refer to a table with statuses will weigh less.

  3. Quick search. In order for the DBMS to compare for equality a string, a character-by-character comparison is performed, which is not found in the numbers.

  • Well, in general, in the end, they came to that. I was forbidden to hang a foreign key on a text string, but they allowed me to hang it on a numeric one. Made the statuses id, code: integer, name: string and foreign key hung on the code. In the end, just doing User.set_status (2). And the development is more convenient, and no one could come up with shortcomings. Thanks to all. - Nekifirus