0

I'm about to design a db for a new project and I'm kinda stuck on some "concept" stuff.

My initial question is very similar to this one. Relational table naming convention

That is: "If I got a table "user" and then I got products that only the user will have, should the table be named "user-product" or just "product"? This is a one to many relationship."

In the above thread I found the answer by PerformanceDBA very useful and well written, but I'm not sure about some points. Quoting part of the answer:

"It doesn't matter if user::product is 1::n. What matters is whether product is a separate entity and whether it is Independent, ie. it can exist on its own. Therefore product, not user_product. And if product exists only in the context of an user, ie. it is Dependent, then user_product."

This is a very interesting point, but generates another question: what exactly are the definitions of Independent and Dependent table?

Example 1, we have two tables:

The table User

Id
Username
FullName

The 1::n table Message, representing a collection of messages sent by the users

UserId (FK to User.Id)
Text

The Message table is dependent from the User table or not? The question I'm asking to myself here, is: "Would the message entity exist without the user?" but I'm not sure about the answer, because it would be "the message would exist but would be anonymous." is this enough to make the Message table dependent from the User table (so i should name the table "UserMessage")?

Example 2, we have two tables:

The table User

Id
Username
FullName

The 1::1 table Profile, representing a user profile

UserId (FK to User.Id)
First Name
Last Name
Gender

Same question, is the table Profile dependent by the User table? I think it is, because a profile without a user would not really make sense.

I'm not sure though, so how can I decide safely if a table is dependent by another or not?

Community
  • 1
  • 1
Wminded
  • 15
  • 3

1 Answers1

0

I think you may really have 3 entities to consider. User, product and user_product. Test relationships by describing them with a verb. The relationship between a user and a product is most likely a many-to-many (a user can order many products, and a product can be ordered by many users). This indicates that a composite table between them that takes the primary keys of both tables is needed (and maybe attributes only if they describe a fact about the user/product combination). user_product is what links a user with his products (and a product with who ordered it) and is thus dependent.

That said, in your examples the message and profile tables are dependent, since they cannot exist without a user (their primary key). Use user - user_message and user - user_profile.

Another example of an independent table would be a lookup table (code/description table).

To answer your last question, an entity is considered dependent if its primary key must exist in another entity before it can exist i.e you can't have a profile without a user so it is dependent.

Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • I agree with you for my second example (user profile), but I'm still not sure about the first one (user message). Let's say an user writes a message, so his id and the message text is written in the Message table; then the user deletes his account. The message will still exist, but would not be "by any user" (Message.UserId would be NULL). In this case, is still the table Message **dependent** from the table User? – Wminded Mar 20 '14 at 10:01
  • If the user is deleted, so should his messages (cascading delete). This should be enforced by a database constraint. Otherwise you risk orphan rows (messages with no user). If you need to preserve messages for a deleted user, then a user should not be deleted, but marked inactive with a flag or something, or user and message data moved to archive tables. The bottom line is that whether a table is dependent or not depends on the requirements and rules of the data you are modeling. – Gary_W Mar 20 '14 at 13:26
  • Ok, so what if I want to store the message even if the user is deleted? Let's say, for statistic purposes or maybe I want to show the message anyway as sent by anonymous? In this case I would set the costraint as ON DELETE SET NULL. This would make the Message table independent instead? So basically the dependency depends on the costraint setup? (ON DELETE CASCADE = dependent? ) – Wminded Mar 20 '14 at 14:53