Database Structure for 1-many-many

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.

• Listing: A single post with title text, post date and simple other info. Will Connect with user table to get username.

• Tags: Each listing can have several tags belonging to a 'type开发者_开发百科' Example:

  Genre: Action, Supernatural, Comedy

  Producers: Sunrise, Bones

• Extra Fields: Each listing can have extra fields too Example:

  Air Dates: 18th Dec 2010

  Duration: 120 mins

The Normalized way would be something like:

-- Listing_table (list_id, user_id, list_title, list_content, list_date)

-- Tags_table (tag_id, tag_type, tag_name, tag_slug)

-- Tags_Listing_table (list_id, tag_id)

-- Field_table (list_id, field_name, field_value)

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?

Also each listing will load multiple threads and inside those threads will be multiple posts, all on the same page kinda like:

[SINGLE PAGE]

Listing (title, content, tags, extra fields)

• Thread 1
  • Post 1
  • Post 2
• Thread 2
  • Post 1
  • Post 2

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.

---

Whether you're aiming for scalability or performance, you'll almost certainly regret this:

-- Field_table (list_id, field_name, field_value)

For some of the specific reasons, see SQL Antipatterns Strike Back. This structure starts on slide 16. Also read Bad CaRMa, which has to do with both scalability and performance.

---

This looks like a typical entity-attribute-value approach. This model is fine, especially for high traffic websites, but it does not lend itself well to SQL. Instead of:

select * from Listing_table where postdate > '2011-02-01'

You'll find yourself writing queries like:

select  *
from    Listing_table lt 
where   (select value from field_table ft where lt.list_id = ft.list id 
         and field_name = 'postdate') > '2011-02-01'

And that's just for a simple date compare. This gets complex really really fast.

Basically, EAV is a tradeoff where you use the database mostly for persistant storage and less for logic and report generation.