Search code examples
primary-keyentity-relationshipcomposite-primary-keyerdvisual-paradigm

ERD - Subclass with composite key that is different than superclass

I am creating an ERD and one of my subclasses has a different PK than its superclass is it ok to do so?

I have a superclass Accounts which which has Username as its PK and then BannedAccounts which has (Foreign Key) AccountsUsername and BanDate as its PK.

The reason I did so is because the same account can be banned multiple times.

Is it correct?

Here is an image of the diagram: https://prnt.sc/vfb56o

Solution

  • Yes, that is fine, and it is common to do this. The table BannedAccounts could be called a fact table since it forms a time series of data points for an account. On the other hand, the Accounts table is a dimension table because it is used to categorize fact data.

    Another way to talk about it is to categorize entities as weak or strong. Account would probably be a strong entity because it does not depend on other entities to exist. In contrast, BannedAccounts is a weak entity because it's existence is dependent on Accounts; it doesn't make sense to talk about which accounts are banned if there is no definition of an account.

    To define the relationship, BannedAccounts is a child and Accounts is a parent because BannedAccounts references the primary key of Accounts. The relationship can be further classified as a strong (or identifying) relationship because the primary key of BannedAccounts contains the entire primary key of Accounts. If the key for Accounts was composite (consisting of more than 1 column), and BannedAccounts did not include all of those columns in it's primary key, then the relationship would be called weak or non-identifying.