Help With Database Layout

Answer 1

I wouldn't have a group of columns for every type of listing, but a "posting_type" column:

CREATE TABLE postings (
posting_id INT NOT NULL AUTO_INCREMENT,
posting_type char(1),             <<<"J"=job,"S"=for sale, etc.
posting_text LONGTEXT NULL,
posting_date DATETIME NULL,
posting_city_id INT NULL,
PRIMARY KEY(posting_id)

Answer 2

I'd consider a nosql solution here like mongodb. Are there any real relational constraints you want to capture here aside from the city-zip relationship?

It seems like the answer would be no here.

@Konerak is right, get something started and build from there.

Answer 3

CREATE TABLE postings ( 
posting_id INT NOT NULL AUTO_INCREMENT, 
for_sale LONGTEXT NULL, 
for_sale_date DATETIME NULL, 
for_sale_city_id INT NULL, 
jobs LONGTEXT NULL, 
jobs_date DATETIME NULL, 
jobs_city_id INT NULL, 
PRIMARY KEY(posting_id) 

This isn't what you asked but seeing this structure tells me you need to create a related table (And a lookup table for the values to choose from) instead of doing things this way. If you are listing the same things repeatedly in a table, you need a related table. I would have postings and Posting_type (since you have more than one type you want listed per posting. Something more like this structure.

CREATE TABLE postings ( 
posting_id INT NOT NULL AUTO_INCREMENT, 
posting_description LONGTEXT NULL, 
Posting_date DATETIME NULL, 
PRIMARY KEY(posting_id) 

CREATE TABLE posting_categories ( 
posting_id INT NOT NULL, 
Category_id int
Primary Key (posting_id, Category_id)

CREATE TABLE Categories ( 
category_id INT NOT NULL AUTO_INCREMENT, 
category_description LONGTEXT NULL, 
PRIMARY KEY(category_id) 

This gives you the freedom to add as many categories as you like without changing the table structures.