关于主键概念与数据库规范化的困惑及技术问题咨询

Great questions—normalization and primary key design can feel tricky at first, but let’s break these down clearly.

1. Does using username+password for authentication violate normalization when the table has id as primary key? #

Absolutely not. Let’s clarify two key points here:

• Primary keys exist to uniquely identify rows, not to dictate how you query the table. Your id primary key does its job perfectly by ensuring each login record is unique and provides a stable identifier for any joins or updates you might need later.
• Normalization rules are about reducing redundant data and avoiding update anomalies, not restricting query conditions. Using username+password for authentication is a business requirement—you need to verify the user’s credentials, which naturally involves checking those two fields.

That said, you should add a unique constraint to the username field. Since usernames should be unique per user, this enforces data integrity at the database level and makes your authentication queries more efficient (the database can quickly look up the username without scanning the entire table). This is still fully compliant with normalization rules.

2. Is the employer + job table design with separate primary keys and a foreign key violating normalization? #

This is actually a perfectly normalized design for a one-to-many relationship (one employer can have multiple jobs). Here’s why:

• The employer table’s id acts as a stable, unique identifier for each employer.
• The job table has its own id primary key (which uniquely identifies each job posting) and a foreign key linking back to employer.id (which associates the job with its employer).

This setup follows core normalization principles:

• No redundant data: You don’t repeat employer details (like name, address) in every job record—you just reference the employer’s ID.
• No update anomalies: If an employer’s information changes, you only need to update it once in the employer table, not across every related job entry.

Querying job data by joining the two tables (using the foreign key) is exactly how relational databases are designed to work. There’s no normalization issue here—this is the standard way to model this kind of relationship.

内容的提问来源于stack exchange，提问作者Son Goku