0

First of all, i've read this, this and this topic about the matter. I think i understand it, but i'm still not sure if i'm doing it the right way, since i almost only get identifying relations. I basically want to know if i'm heading in the right direction.

I'm creating a database where users, amongst other things, can create a profile (with a free and payed version) and post news items. Other users can reply to these items, and reactions can receive a rating. This is a small part of the website's functionality, but it's the part that worries me. I've wrote down the following (each row represents a link between two tables):

USERS:
One user can define additional info once [Identifying, 1:1]
One user can have one sexual preference [Identifying, 1:1]
One user can have one payment status [Identifying, 1:1]

One user can define five questions [Identifying, 1:n]
One user can define three additional information rows [Identifying, 1:n]
One user can define three interests [Identifying, 1:n]

NEWS:
One user can post multiple news items [Identifying, 1:n]
One news item can receive multiple comments [Identifying, 1:n]
Each comment has one author (user) [Identifying, 1:1]
Each comment can receive one rating [Identifying, 1:1]
Each rating can be placed by one user at a time [Identifying, 1:1]

PAYMENT:
One promotion can either apply for all users or for one specific user [Non-identifying, 1:n]

Almost all of the above relations are identifying, which makes me question wether i understand the matter.

Besides that, i use a table (status) which hold (amongst others) the payment status of a user and another table (promotions) that holds possible promotions. It's not clear to me wether i'm suppose to link the promotions table to the status or user table.

Can someone help me out with the doubts?

Community
  • 1
  • 1
Maurice
  • 1,082
  • 1
  • 20
  • 43
  • Although each comment has one author, surely each author can make many comments - so surely the comment to author relationship is n:1? Also, surely each comment can receive many ratings (one from each user), so the comment to rating relationship is 1:n, and one user can make many ratings so the rating to user relationship is n:1? –  Mar 26 '13 at 13:04
  • Makes sense, thnx! So as far as i understand the other part, a non-identifying relationship is quite rare..? – Maurice Mar 27 '13 at 07:52
  • Not necessarily - note that my previous comment was solely about the *cardinality* of some of the relationships, not whether they were identifying/non-identifying relationships. As Bill Karwin wrote in the highest rated / accepted answer to this question: http://stackoverflow.com/questions/2814469 (which you linked in your question), "The technical definition of an identifying relationship is that a child's foreign key is part of its primary key". As such, it's going to depend on which fields you use as keys in your design. –  Mar 27 '13 at 11:08

1 Answers1

0

I dont know if I answer exactly to your question especially considering your identify and non-identify relationships.

From what you say, it looks like Status table and Promotion table are what I call referential tables that can be linked to User table on (using entity association model) a 1-n relationship meaning that there is a foreigh key in User table referencing the status id and another one refenrencing the promotion id (this means you dont want any historic) -- probably 0-n concerning the promotion

The table news is linked to User by the same king of realationship with the n cardinality on the User table side (which means that the foreign key in in the News Table referencing the user id, idem for Comment table to New table with the foreign key referencing news id inside comments table

Concerning the Rating table, without thinking too much, I would say that it commes from an association from User and Comment with a n-n relationship. Consequence : a table with user_id and comment_id foreign key inside... The rating for a comment is the result of an operation on the rating table but to go faster you could had a trigger on the rating table saying that everytime a rating is added on a comment you calculate the new rating that you can push a field in the comment table

mlwacosmos
  • 4,391
  • 16
  • 66
  • 114
  • I (think i) can follow your story, but am still breaking my head over de identifying vs non-identifying relationships.. Thanx anyway! – Maurice Mar 27 '13 at 07:57