[eluser]Dregond Rahl[/eluser]
Hey everyone,
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:
Code:
Genre: Action, Supernatural, Comedy
Producers: Sunrise, Bones
- Extra Fields: Each listing can have extra fields too Example:
Code:
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?
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.
