I'm a Rails novice engineer.
I would like to build a new web application (marketplace) and deploy it on AWS.
With the launch of a new service, we plan to proceed with full scratch, and we will design the DB from scratch, including data.
We plan to use RDB (MariaDB) as the DB.

Currently, reference books:DB design learned from mastersI have read through and started the logical design of the database, but I do not fully understand "primary key/natural key/surrogate key", so let me ask you a question.

Understanding the current situation

This is an excerpt from a reference book, but I understand that the general principle is to avoid using surrogate keys as much as possible and model with the natural key as the primary key.

On the other hand, is Rails (Active Record) forced to surrogate keys?
Reference site 1:Rails table name and primary key are coc and highly readable thanks to strict rules
Reference site 2:Alternate key and natural key


Q1. Is the ID automatically issued when registering a record on the DB side called a surrogate key (proxy key)?
(Is the column user_id prepared by default when creating the User table the column corresponding to the surrogate key?)

Q2. Is it necessary to define and issue a unique code system separately from the ID automatically issued on the DB side? Specifically, as a general theory of marketplace (EC site), for example, for product tables and shop tables, in addition to product_id and shop_id automatically issued on the DB side, definition and issuance of code systems such as product code and shop code Do you need a turn?

  • Answer # 1

    Perhaps the impression is that we do not know the difference between a logical model and a physical model.

    Logical models, as the name implies, perform logical modeling of problem domains.
    In that case, it is unnatural for surrogate keys to appear to represent the uniqueness of an entity.

    The uniqueness of an "employee" entity is naturally represented by an "employee number", and it is unlikely that something like a "mechanically assigned ID" will appear.

    In the first place, "mechanically assigned ID" cannot appear in the analysis stage of the business domain.

    When hearing requirements from your customers, there is no reason to say, "On this system, our employees manage with the mechanically assigned ID generated by the DB." The DB is about to be created and doesn't exist yet.
    From the user's point of view, there should be a natural key such as "employee number".

    Since the physical model is a pair with the actual program, it is natural that the "mechanically assigned ID" that appears for the convenience of programming appears.

    In that case, the natural key is not always appropriate to actually use to uniquely identify the "employee" record programmatically.

    In addition to not being able to follow Rails' default rules like the question text, if the employee number is a character string type instead of a numeric type like "A0001", it is not preferable for performance to search using this as the primary key. Is also possible.