1

I have a 3NF normalized database design and was trying to understand the concept of strong vs weak entity types and my understanding is weak entity types are tables that don't have a primary key. However, surely all tables should have a primary key thus making them all a strong entity? So all my tables are strong entities? or am I missing something?

Rethinking, my junction tables have composite keys, (in maria db too make a composite you do PRIMARY KEY(column1, column2)) so would these class as weak entities?

Database design: enter image description here

5
  • 1
    MySQL <> MariaDB.
    – philipxy
    Jan 9, 2022 at 21:04
  • You are expecting a "customer" to have multiple "accounts" and multiple "addresses"? Don't use many-to-many tables when the relationship is not many-to-many.
    – Rick James
    Jan 10, 2022 at 20:58
  • @Rick James what? I’ve made a many to many table because it’s a many to many relation Jan 11, 2022 at 21:03
  • Two customers share the same account? Share the same address?
    – Rick James
    Jan 11, 2022 at 23:17
  • @Rick James yes there is something called a joint account? and people can live in the same address that usually how a house works... Jan 12, 2022 at 11:17

1 Answer 1

6

I've seen it mentioned in blogs and tutorials that a weak entity "doesn't have a primary key," but this is misleading.

You are correct that every table needs a primary key. This is necessary for the table to qualify as a relation. Every relation has one or more candidate keys, and one of the candidate keys is chosen as the table's primary key. The meaning of a candidate key is that the columns are non-NULL and they can be used to uniquely identify any row in the table.

The term weak entity is used for a table where the rows cannot exist without referencing some "parent" entity. For example, you might have tables Users and Phones such that a user can have zero, one, or multiple phones. But a phone number cannot exist without referencing its owning user.

Therefore Users is a strong entity; one row in the Users table can exist without regard to how many phones it has, or even if no phones reference it.

Phones is a weak entity. A given phone number cannot exist unless it references a row in Users.

A good way to implement this in SQL is to require that the primary key of the Phones table include a reference to the primary key of a row in User. But a user may own multiple phones, so there needs to be an additional column in Phones to make it a compound primary key. This is often called the discriminator and it's just an arbitrary value that serves to be distinct for each of the phones belonging to a given user. It doesn't necessarily have any other meaning, but sometimes people use it as an ordinal number for displaying the phones.

Example:

CREATE TABLE Phones (
  owner_id INT NOT NULL,
  phone_ordinal INT NOT NULL,
  phone_number VARCHAR(32) NOT NULL,
  phone_type VARCHAR(16) NOT NULL,
  PRIMARY KEY (owner_id, phone_ordinal),
  FOREIGN KEY (owner_id) REFERENCES Users(user_id)
);

Some people state this as "Phones doesn't have a primary key" but they mean it doesn't have its own auto-incrementing integer column. It depends on values in the primary key of the Users table.

Many software developers confuse "primary key" with "an auto-incrementing integer column named id." This is a misunderstanding of what a primary key is. A primary key is a constraint applied to one or more columns, which together are a candidate key for the table. It does not necessarily mean the primary key is a single column, or that it is an integer, or that it is auto-incrementing.


P.S. I want to be clear that using auto-increment for a primary key is not the criterion for a strong entity. Auto-increment is not required by the relational model at all, it's just a convenience that is popular.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.