StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTwo Mysql Tables Interlocked by same 2 keys
text
Body
copied!<h1>Table 1</h1> <pre><code>Table1_PK (PK, AI) Table2_PK </code></pre> <h1>Table 2</h1> <pre><code>Table2_PK (PK, AI) Table1_PK </code></pre> <p>As you can see these two tables are intertwined in the sense that one table requires the other table's Primary Key value and vice versa.</p> <p>Creating these tables requires that I run the first table insert with a "fake value" for the second value and then run the insert query on the second table. And finally update the first table.</p> <h1>Question</h1> <p>Is this a poor database schema? And is there a specific solution to "solving" for the two keys in one statement?</p> <h1>---Update---</h1> <p>Thank you everyone for the wonderful insight. I have reconsidered my approach...</p> <p>I am modeling a social networking website that enables users to repost anything whether its a post, status, picture, etc.</p> <p>Each of these re-postable elements has a running vote but at the same time, each requires a separate table for each of their unique data (i.e. picture requires a caption and pic_id but a status would not require those data elemetns).</p> <p>Table 1 represents a summary of all respostable content. It contains a unique id for the reposted content and running vote total (plus other columns).</p> <p>Table 2 is not just one table - it is the basic table skeleton for the picture_table, post_table, status_table, etc. Each of these tables would differ in layout but originally, I wanted all of these tables to have a reference to Table 1 to quickly fetch the vote data and other "common" information.</p> <p>Adding names to the model, we have:</p> <h1>Table 1: Repostable Content</h1> <pre><code>Reposted_id (PK, AI) Entity_type (post, stauts, picture) Entity_id (post_id, status_id, picture_id) Vote... and other columns </code></pre> <h1>Table 2-# (multiple tables, one for post, status, picture, etc.</h1> <pre><code>Entity_id (post_id, status_id, pic_id, etc.) (PK, AI) Reposted_id (PK) </code></pre> <h2>Reconsideration</h2> <p>After reconsideration, I have decided that Table 2 does not require the reposted_id (table 1 PK) and instead it is possible to query the first table by (entity_type, entity_id) -> and fetch the reposted_id? This would eliminate the circular reference issue.</p> <p>-- Could someone perhaps give me insight if this is a better model? Or if there are any better ways of doing this?</p>

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