StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODatabase Structure for 1-many-many
text
Body
copied!<p>I need some ideas how to handle the database structure for a site i'm working on, i have been whacking my brains over it for a while to figure out the best way to design the tables to help it become scalable. Here are the details.</p> <ul> <li><p>Listing: A single post with title text, post date and simple other info. Will Connect with user table to get username.</p></li> <li><p>Tags: Each listing can have several tags belonging to a 'type' Example:</p> <p>Genre: Action, Supernatural, Comedy</p> <p>Producers: Sunrise, Bones</p></li> <li><p>Extra Fields: Each listing can have extra fields too Example:</p> <p>Air Dates: 18th Dec 2010</p> <p>Duration: 120 mins</p></li> </ul> <p>The Normalized way would be something like:</p> <p>-- Listing_table (list_id, user_id, list_title, list_content, list_date)</p> <p>-- Tags_table (tag_id, tag_type, tag_name, tag_slug)</p> <p>-- Tags_Listing_table (list_id, tag_id)</p> <p>-- Field_table (list_id, field_name, field_value)</p> <p>Would this structure be fine ? Also what would be the best way to query all this information efficiently. I don't think its possible at all to get all this in one query. what are my options?</p> <p>Also each listing will load multiple threads and inside those threads will be multiple posts, all on the same page kinda like:</p> <p>[SINGLE PAGE]</p> <p>Listing (title, content, tags, extra fields)</p> <ul> <li>Thread 1 <ul> <li>Post 1</li> <li>Post 2</li> </ul></li> <li>Thread 2 <ul> <li>Post 1</li> <li>Post 2</li> </ul></li> </ul> <p>Thanks for everyone who helps, I really would appreciate some insight you all. If there is anything further I can add to help you help me please ask. I can dump my SQL structure.</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