英文:
How would these Go structs look as Postgres tables?
问题
我不知道如何将这些结构体写成SQL表格，请帮忙。
type Author struct {
ID string `json:"id"`
Name string `json:"name"`
Votes int `json:"votes"`
}
type Comment struct {
ID string `json:"id"`
Author Author `json:"author"`
Content string `json:"content"`
Votes int `json:"votes"`
}
type Post struct {
ID string `json:"id"`
Title string `json:"title"`
Content string `json:"content"`
Author Author `json:"author"`
Comments []Comment `json:"comments"`
Votes int `json:"votes"`
CreatedAt time.Time `json:"created_at"`
}
我在Author和Comments这里卡住了，因为在Go语言中，它是一个结构体的数组...
CREATE TABLE IF NOT EXISTS posts (
id CHAR(32) PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content VARCHAR(255) NOT NULL,
author ??? NOT NULL, -- 这里填什么
comments ??? NOT NULL, -- 这里填什么
votes int NOT NULL,
created_at TIMESTAMPTZ NOT NULL
);
英文:
I don't know how to write these structs as SQL tables, please help
type Author struct {
ID string `json:"id"`
Name string `json:"name"`
Votes int `json:"votes"`
}
type Comment struct {
ID string `json:"id"`
Author Author `json:"author"`
Content string `json:"content"`
Votes int `json:"votes"`
}
type Post struct {
ID string `json:"id"`
Title string `json:"title"`
Content string `json:"content"`
Author Author `json:"author"`
Comments []Comment `json:"comments"`
Votes int `json:"votes"`
CreatedAt time.Time `json:"created_at"`
}
I get stuck at Author and Comments because in go it's an array of a struct...
CREATE TABLE IF NOT EXISTS posts (
id CHAR(32) PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content VARCHAR(255) NOT NULL,
author ??? NOT NULL, -- what goes here
comments ??? NOT NULL, -- and here
votes int NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
);
答案1
得分: 3
所以你已经添加了PostgreSQL和SQL。不确定你使用的是哪个版本，但假设是PostgreSQL 11+。
这是一个根据你的结构描述绘制的图表。
作者与评论是一对多的关系
作者与帖子是一对多的关系
帖子有多个评论
帖子有一个作者
评论属于一个帖子
评论属于一个作者
CREATE TABLE IF NOT EXISTS authors (
id UUID PRIMARY KEY,
name VARCHAR(255) NOT NULL,
votes INT NOT NULL
);
CREATE TABLE IF NOT EXISTS comments (
id UUID PRIMARY KEY,
post_id UUID NOT NULL REFERENCES posts(id),
author_id UUID NOT NULL REFERENCES authors(id),
content VARCHAR(255) NOT NULL,
votes INT NOT NULL
);
CREATE TABLE IF NOT EXISTS posts (
id UUID PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
author_id UUID NOT NULL REFERENCES authors(id),
votes INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL
);
不过，我会对你的设计提出一些建议。你确定一篇帖子只有一个作者吗？
评论的内容可能很长，你想将其限制在255个字符内吗？如果是的话，可能需要将其定义为TEXT类型。
英文:
So you've added postgres and sql. So not sure which one you are using, but assuming its PostgreSQL version 11+
This is how it would look as a diagram, with the relationships your structs describe.
Author is one to many comments
Author is one to many posts
A Post has many comments
A Post has one author
A comment belongs to a post
A comment belongs to an author
CREATE TABLE IF NOT EXISTS authors (
id UUID PRIMARY KEY,
name VARCHAR(255) NOT NULL,
votes INT NOT NULL
);
CREATE TABLE IF NOT EXISTS comments (
id UUID PRIMARY KEY,
post_id UUID NOT NULL REFERENCES posts(id),
author_id UUID NOT NULL REFERENCES authors(id),
content VARCHAR(255) NOT NULL,
votes INT NOT NULL
);
CREATE TABLE IF NOT EXISTS posts (
id UUID PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
author_id UUID NOT NULL REFERENCES authors(id),
votes INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL
);
A couple of things I'd look at your design though. As you sure a post would only have a single author?
Content could be quite large on a comment, do you want to limit this to 255 or more? If so you'd probably want this as TEXT?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论