Advice on SQL tables, circular reference and foreign keys.
I'm very new to SQL (about a month or so), so please forgive any subsequent unfortunate naivete. I working on a project on stories where a user can start a story and another user can add to that story. At the moment, my two main tables are stories and paragraphs. Stories are made up of paragraphs. Paragraphs are just a chunk of text. The stories schema looks like this:
stid varchar not null primary key,
title text not null,
description text,
created_at timestamptz DEFAULT now()
The paragraph schema looks like this:
prid bigint not null primary key,
story varchar not null REFERENCES stories(stid),
maintext text,
writer text not null REFERENCES users(username),
parentpr bigint, //the previous paragraph
childpr bigint, //the next paragraph
created_at timestamptz DEFAULT now()
I am thinking of adding a headpara and lastpara column to the stories schema (with an ALTER) so I can easily access the first paragraph and last paragraph but that creates circular reference situation, since stories will ref paragraph and vice versa. Is this okay? Will it become a more when I begin to deal with larger amount of data and queries?
I have thought of a solution where I have another table: story-paragraph assignment. schema:
ID primary key
story REFERENCES stories(stid),
headpara REFERENCES paragraph(prid),
lastpara REFERENCES paragraph(prid)
for some reason, I am not convinced of this solution. It feels redundant to me. This is not a many-to-many situation. But paragraphs needs to reference stories and I need to be able access the first paragraph and last paragraph of a story.
Another possible solution could be having two boolean columns in the paragraph schema called head and tail so first paragraph can be called with
WHERE story == stID AND head == True.
Thoughts? This solution seems like it would be an issue when my paragraph table is very large. Many thanks in advance.
You can do the solution either way. If you know that the head paragraph and last paragraph are really important, then having references to them in the story is fine.
In either case, there is a bit of a challenge maintaining relational integrity. Presumably, you want the head and last paragraphs to be in the same story. For this, you will want a composite key. And you need to add the key using a separate alter table statement. So:
alter table paragraph add constraint unq_paragraph_story_prid unique (story, prid);
alter table stories add constraint fk_stories_headpara
foreign key (stid, headpara) references paragraph(story, prid);
alter table stories add constraint fk_stories_lastpara
foreign key (stid, lastpara) references paragraph(story, prid);
Similarly, if you use the flags, you will need to ensure that there is exactly one flag of each type set. That can be a bit of a pain when updating. That constraint would look like:
create unique index unq_paragraph_headpara paragraph(story) where head = 1;
create unique index unq_paragraph_lastpara paragraph(story) where last = 1;
Notes about naming and other things:
ids should be numeric, if they can be. This simplifies foreign key references.paragraphId or paragraph_id) or simply id. If you use prid, that could get confused with another table.この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加