开发者

Can my database have a triangular relationship?

开发者 https://www.devze.com 2022-12-21 18:18 出处:网络
Take tables: User, Comment, Snippet. A User can have many Snippets. A Snippet can have many Comments. A User can leave many Comments.

Take tables: User, Comment, Snippet.

A User can have many Snippets. A Snippet can have many Comments. A User can leave many Comments.

In turn, when I diagram things, I end up with something like a triangle.

User 1-------------* Comment
  开发者_开发百科    \           / 
       \         /
        *Snippet 1


Of course a database can have relationships like that:

Users
  id
  name
  address

Snippets
  id
  user_id
  body

Comments
  id
  body
  snippet_id
  user_id

Examples:

--Get all comments by a user
SELECT * FROM comments WHERE user_id = 1

--Get all snippets by a user
SELECT * FROM snippets WHERE user_id = 1

--Get all comments on a snippet
SELECT * FROM comments WHERE snippet_id = 1

--Get all comments on a particular snippet by a particular user
SELECT * FROM comments WHERE snippet_id = 1 AND user_id = 1


Absolutely.

create table Users (Id int not null primary key identity(1,1))
create table Snippets (Id int not null primary key identity(1,1), 
                       UserId int not null)
create table Comments (Id int not null primary key identity(1,1),
                       SnippetId int not null,
                       UserId int not null)

Set up your foreign keys and you're all set.

0

精彩评论

暂无评论...
验证码 换一张
取 消