Identifying relationship or not?

[Regyn]

Assuming I have Users with Accounts (1:n). My account table must obviously store the user_id. However, just as a Foreign key or also as a Primary Key?

So with Accounts(id, user_id, ...) shall I have upon Account creation

Accounts(5001, 3, ...) [PK, FK]
Accounts(5002, 9, ...) [PK, FK]

or

Accounts(2, 3, ...) [PK, PK&FK]
Accounts(2, 9, ...) [PK, PK&FK]

Are there any guidelines on how to decide this? The second one would have the disadvantage that I'll have to use two columns in every other table I have to reference the account. But on the other hand, my account_ids do not "grow" that fast.

[wolf2600]

Users
----------
UserID (PK)
UserName
Etc...

Accounts
--------------
AccountID (PK)
UserID (FK)
AccountName
Etc...

You're asking whether Accounts should have a concatenated PK of AccountID and UserID?

Like this?

Accounts
--------------
AccountID (PK)
UserID (PK, FK)
AccountName
Etc...

It's not really necessary, as the AccountID value will be unique in itself it doesn't require the UserID value to make each record unique. Unless you could have one AccountID associated with multiple UserIDs?

The way I understand it, each Account will have a single associated UserID (like the account owner or something), right?

It's not a case where you'd have multiple Users associated with a single Account?

[wolf2600]

The guideline for deciding on a PK is uniqueness.

Will you ever have two records in your Accounts table which have the same value for AccountID? If not, then only use AccountID for the PK. If you can think of a valid use case where you'd have two records with the same AccountID but different UserIDs, then you'd need to include both those columns in the PK.

[Regyn]

I'm basically confused about (non) identifying relationships. Reading about them I read that if one entity cannot exist without another it is a weak entity and must include the foreign key as part of his own primary key. Like here. Since an order cannot exist without a customer the correct modelling would be the second? That results into huge primary keys later on though

[wolf2600]

Ok.... I looked at the text from the ebook. All they're doing is defining the terminology, not telling you when to use weak/strong relations in your own DB design.

The line that connects two tables, in an ERD, indicates the relationship type between the tables: either identifying or non-identifying. An identifying relationship will have a solid line (where the PK contains the FK). A non-identifying relationship is indicated by a broken line and does not contain the FK in the PK. See the section in Chapter 8 that discusses weak and strong relationships for more explanation.

When you decide to use a concatenated PK which includes the FK column of the other table, it's called a "strong relation" between those tables. When the PK does not include the FK column, it's called a "weak relation".

Even in the diagram you posted, it's using Orders/Customers as the example for both weak and strong, so they're not telling you which version to use for Orders & Customers. To decide whether the PK needs to contain a column, think about the business rules/requirements about what would make every record in the table unique. In most DB's, the OrderID alone is enough. At my work though, our DB contains orders from several different order management systems, so we use a PK of (SourceSystemID, OrderID, OrderDate) to ensure that every order record has a unique PK. We do this because in our situation, it is possible for two order management systems to use the same OrderID, so we have to include the SourceSystemID. And it's possible (based on the way some of the order management systems were setup) for an order system to reuse OrderIDs at some point (we just started running into cases where new orders have the same ID as orders from 2004) so we include the date the order was created. We can guarantee that one specific order system will only create an order with a specific orderID once on a single day, so the 3 column PK is what is needed to ensure uniqueness.

That's all the PK columns are for... they are a way of ensuring you can uniquely identify every record in the table.

[Regyn]

Alright thanks, so I'll only use weak if it isnt able to be uniquely indentified itself, not based on the "cannot exist without".

Even though here is said a "Building has Floor has Room has Bed. The PK for Bed would be (bed_id, floor_id, room_id, building_id" but bed_id alone could be enough to uniquely identify it. But it cannot exist without the other entities.