StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Rysiek, if I understood correctly, you have text column in <code>foos</code> table that is parsed to extract foreign keys pointing to <code>bars</code> table. This approach to building relations may be justified in some cases, however almost every guide/tutorial to database programming would discourage doing so. Why not use standard foreign key in <code>bars</code> that would point to foo in <code>foos</code>? Unless there is a requirement for bars to be assigned to more than one foo. If so, this identifies your relation as many-to-many rather one-to-many. In either situation using standard foreign key based solution seems much more natural for database.</p> <p>Example db schema for one-to-many relation:</p> <pre><code>CREATE TABLE foos ( id SERIAL PRIMARY KEY, .... ); CREATE TABLE bars ( id SERIAL PRIMARY KEY, foo_id INT REFERENCES bars (id) ON DELETE CASCADE, ... ); </code></pre> <p>And the same for many-to-many relation:</p> <pre><code>CREATE TABLE foos ( id SERIAL PRIMARY KEY, .... ); CREATE TABLE bars ( id SERIAL PRIMARY KEY, ... ); CREATE TABLE foostobars ( foo_id INT REFERENCES foos (id) ON DELETE CASCADE, bar_id INT REFERENCES bars (id) ON DELETE CASCADE ); </code></pre> <p>I would also recommend using INNER JOIN instead of table multiplication (SELECT FROM foos, bars).</p> <pre><code>CREATE VIEW foobars AS SELECT foos.id AS foo_id, foos.something, bars.id AS bar_id, bars.somethingelse FROM foos INNER JOIN bars ON bars.foo_id = foo.id; </code></pre> <p>The same for many-to-many INNER JOINS</p> <pre><code>CREATE VIEW foobars AS SELECT foos.id AS foo_id, foos.something, bars.id AS bar_id, bars.somethingelse FROM foos INNER JOIN foostobars AS ftb ON ftb.foo_id = foo.id INNER JOIN bars ON bars.id = ftb.bar_id; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload