Efficient Classifieds Mysql Structure

I am restructuring a classifieds MySQL db where the different main sections are separated into separate tables. For example, sale items have their own table with unique ID's, jobs have their own table with unique ID's, personals have their own table as well.

These sections all share a few common characteristics:

-id

-title

-body

-listing status

-poster

-reply email

-posting date

But they each have some separate information required as well:

-each have different sets and trees of categories to choose from 开发者_如何学编程(which affect the structure needed to store them)

-jobs need to store things like salary, start date, etc.

-sale items need to store things like prices, obo, etc.

Therefore, is it a better practice to refactor the db while I can to a universal table to store ALL the general listing info regardless of section, and then task out customized data storage to small tables, or is it better to leave the current structure alone and leave the sections separated?

Sounds like they are all separate entities that have nothing to do with each other (ecxept for sharing some column-definitions), right?

Do you ever want to do a SELECT like

SELECT *
FROM main_entity
WHERE entity_type IN ('SALE_ITEM', 'JOB', 'PERSONAL')?

Otherwise I don't think I would merge them into one table.

Don't use a single table. Go relational.

What I would recommend setting up is a so-called polymorphic relationship between your "main" table (the one with the common characteristics), and three tables containing specific information. The structure would look something like this:

Main table

• id
• title
• ...
• category_name (VARCHAR or CHAR)
• category_id (INTEGER)

Category table

• id
• (specific columns)

The category_name field should contain the table name of the specific category table, eg. 'job_category', while the category_id should point to ID in the category table. An example would look like this:

# MAIN TABLE
id  | title         | ... | category_name | category_id
-------------------------------------------------------
123 | Some title    | ... | job_category  | 345
321 | Another title | ... | sale_category | 543

# SPECIFIC TABLE (job_category)
id  | ...
---------
345 | ...

# SPECIFIC TABLE (sale_category)
id  | ...
---------
543 | ...

Now, whenever you query the main table, you will immediately know which table to fetch the additional data from, and you will know the ID in that table. The only downside to this approach is that you have to perform two separate queries to fetch information for one single item. It would probably be possible to do this in a transaction, however.

For fetching data the other way around (eg. you search the jobs_category for something), on the other hand, you can fetch the associated data from the main table with a JOIN. Remember to not only join main.category_id = jobs_category.id, but also to use the category_name column as a join condition. Otherwise, you may fetch data that belongs to one of the other categories.

For optimal performance, you may want to index the category_name and category_id columns. This would mostly speed up any queries that join the two tables, as described in the previous paragraph.

Hope this helps!