i don't think normalization is the problem here, you'll probably end up with a user- and a category-table and the table(s) to hold your ads (yes i know there will be probably some more tables for the app, but let us not complicate things).
The ads table is the interesting part: you have two and a half choice.
- 1) one table to hold them all: one table that has all fields needed for all categories
- pro: easy to setup
- pro: easy to maintain (only one table to backup, to alter etc)
- pro: very easy SQL which simplifies front end development
- con: doesn't scale very well
- con: wastes some space, which will slow down the database in the long term (depends on database and table structure though)
- 2) one table per category
- pro: scales better than (1)
- con: very complicated SQL
- con: nightmare to maintain: instead of one table you have to alter possibly 100s
so you see, option (2) is not really a option, even if it scales better. If you plan for a big system roughly the same effect can be achieved with clustering the table by category.
I owe you a half option: if you're not bound to mysql, postgresql could offer a interesting alternative: table inheritance. in pg you can define a table ads
that holds your base structure and a derived table cars
that contains all the fields of ads
plus some (car specific) additional fields. And a table for electronics, and one for photographic equipment, and so on, you name it. You could even go further and create tables vans
and convertibles
that dont inherit from ads
but from cars
, creating a category tree that represents the object hierarchy of your frontend.
So whats the difference to (2) you might ask? Maintenance is simplified, a structure change in the ads
table propagates down to all the derived tables (while a change in the cars
table would only modify cars, convertibles and vans
as one would expect).
Same goes for the sql: if you select * from ads where title='foo'
, the query will return records from ads
and all the derived tables, the whole tree, if you select from cars
only that subtree is searched .. you get the idea.
There's more to it, your search engine could pull the structure/metadata of your categ tables and create the search interfaces from it, so your search interface is always in sync with the data structure, etc.
Haven't thought this to the end and i'm still not sure if i would build a system that way, but it has something. The system must be very well designed and will have lots of explicit boundaries but that can be a good thing(tm).
One final word about mysql and postgres. pg is a database that IMHO in most aspects is today superior to mysql, is just not that famous. And no, i'm not just a postgres fanboy, i'm a senior mysql user, i started using mysql with a version 2.something, i introduced it more than 10 years ago in the company i still work for (and made it the default database), and today i don't allow any new development i can decide to be based on mysql.
The reason is simple: the default "storage engine" in mysql is myisam, which is fast and lean and offers lots of features .. and you will loose data in the long run if you use it. IMHO you can only use it for volatile data, and there are better alternatives for running a cache. if you have to rely on your data, myisam is a NOGO.
I tested Innodb, the default for a transactional "storage engine", several times over the years, and i never found the performance satisfying, so i went for alternatives.
postgres is not perfect either, but it vastly improved in the last years in terms of usability, administration and speed (feature wise it was leading most of the time anyway, and still is).
ok, enough advocacy for today, i'll go to bed now :-)