My real-estate application has a database table that it holds the queries(inquiries) by the user. This table holds all the information about a particular real-estate query.
The database table looks something like this:
CREATE TABLE IF NOT EXISTS `queries` (
`id` bigint(20) NOT NULL auto_increment,
`category_id` int(10) NOT NULL,
`serial` varchar(30) NOT NULL,
`minBudget` int(11) NOT NULL,
`maxBudget` int(11) NOT NULL,
`city_id` int(10) NOT NULL,
`area_id` int(10) NOT NULL,
`locality` varchar(100) NOT NULL,
`status` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Do NOT use a varchar if those are IDs to another table. Use a many-to-many table mapping them together.
CREATE TABLE IF NOT EXIST `query_cities` ( `id` bigint(20) NOT NULL auto_increment, `query_id` bigint(20), `city_id` bigint(20) ) CREATE TABLE IF NOT EXIST `query_areas` ( `id` bigint(20) NOT NULL auto_increment, `area_id` bigint(20) )
This will be much cleaner than stuffing things into a varchar - for instance, it allows you to say:
SELECT c.city_name, c.state, c.whatever FROM queries q JOIN cities c ON (q.city_id = c.id) WHERE q.id = ?
Edit: meh, I'm lame and didn't include foreign keys, there, but you get the point.