I use meaningless key/artificial key as primary key for all my strong entities. So, lets say I have:
Strong_Entity1(se1_id(PK))
Strong_Entity2(se2_id(PK))
and I have a many-to-many relationship between Strong_Entity1 & Strong_Entity2.

Since I need to resolve that many-to-many rel to 2 1-to-many relationships, I'll have a weak entity in the middle:
Weak_Entity(se1_id(PK), se2_id(PK))

As we know both se1_id(PK), se2_id(PK) are foreign keys from the 2 different strong entities. Do you recommend to use these 2 foreign keys as a single composite primary key for the weak entity or, again, create a new meaningless single field ID primary key for the weak entity ?

I need to know why. Understanding why is important to me... as every design needs to have a reasoning/justification behind it.

Thanks.

If the weak-entity exists solely or primarily to combine/link the strong entities, then I'd use a composite key of the combined strong entity keys. If the weak-entity had significant additional information, I'd probably create another meaningless value to serve as its primary key.

The argument between natural and surrogate keys is one that will probably rage forever. Natural keys are good because they are easy for humans to spot and remember. Surrogate keys are good because humans have no reason to try to monkey with them. I'm a strong believer in surrogate keys, because I've seen the consternation that can be caused when someone gets the bright idea to change a natural key... It has been a mess for me, more than once!

Other people (whose opinions I respect) have never or only very rarely had this problem, so they see much more value in natural keys. They can't imagine that someday some cheerful idiot politician might decide to make Indiana be IA and Iowa be IO... Unfortunately, I've been the victim of similar changes forced upon our industry by those cheerful politicians, and I've been the poor soul that had to fix the resulting mess!

-PatP

Thanks Pat Phelan for the good explaination.

I am a believer too, that's why both my strong entities use meaningless surrogate keys:
Strong_Entity1(se1_id(PK))
Strong_Entity2(se2_id(PK))

My issue now is more about weak entities...those between 2 strong entities.

As far as possible, I want to avoid creating new surrogate key (new table field/column) for weak entities since their foreign keys are already meaningless...and won't be changed by some monkeys. (Is this thinking of mine right ?)

I have thought of a way to clearly determine whether to create new surrogate key for weak entities or not :

As long as I don't use ON DELETE/UPDATE SET NULL / SET DEFAULT (this means you use ON DELETE/UPDATE CASCADE/RESTRICT/NO ACTION), it is ok to use se1_id(PK), se2_id(PK) as the primary key and do not need to create a new meaningless primary key for the weak entity.

But if you have ON DELETE/UPDATE SET NULL / SET DEFAULT, you have to create a new surrogate key for the weak entity's own use. This is because if you use se1_id(PK), se2_id(PK) as its primary key, SET NULL/SET DEFAULT on either side of the referencial integrity constraint will cause the record to be not unique in the weak entity table.

Correct me if I am wrong.

I see this problem at a much more abstract level than what you are considering. Let me make an example to see if that helps...

In a traditional marriage, you have one man and one woman, and they get married one time. In this case, the weak entity represents the logical connection of the two people.

In the real world, people can get married more than once. They can even marry the same person more than once! At least in some legal jurisdictions, one person can be married to more than one other person.

For the first case, the primary key for the weak entity (marriage) could easily be the combined keys from the strong entities (people). This would uniquely identify the row, and the marriage would not have any meaningful data of its own.

For the second case, I would create a new surrogate key. The marriage would track the people involved, but also the date, the place, and other details. The marriage itself would be an item of interest, and there would be data specific to the marriage that was separate from the people involved.

If there is only a tiny amount of data associated with the weak entity, it might not make sense to create a surrogate key for it. In general though, if I store one thing that isn't derived from the notion of the connection itself, I usually create a new surrogate key... As things evolve and the schema gets more complex, that surrogate key usually serves me well.

-PatP