Hmmm.....
--
-- Application Database Structure
--
-- Company Table
CREATE TABLE company (
id SERIAL PRIMARY KEY,
name varchar(60) NOT NULL
);
This probably works, for now. I'm assuming the column can handle weird characters, things like Chinese characters, if appropriate. Please note that international business gets a little strange, in terms of things like subsidiaries, and companies doing business in foreign countries (not sure if important).
-- Country Table
CREATE TABLE country (
id SERIAL PRIMARY KEY,
name varchar(255) NOT NULL
);
You're going to want to add a 'localization' table, in addition to refactoring this. Something along these lines:
-- Country Table
CREATE TABLE country (
id SERIAL PRIMARY KEY,
Iso3166_1_Alpha_3 char(3) NOT NULL,
Iso3166_1_Alpha_2 char(2),
Iso3166_1_Numeric char(3) NOT NULL
-- This last is comprised of digits,
-- But not really a number, per se
);
-- Country Localization table
CREATE TABLE country_localization (
country_id INTEGER NOT NULL REFERENCES country (id),
language_id INTEGER NOT NULL REFERENCES language (id),
name varchar(100) NOT NULL
);
.... Which means you're going to want a 'language' table (and localization) as well. You can use this as the base pattern - the relevant ISO code standard is ISO 639.
-- Location Table
CREATE TABLE location (
id SERIAL PRIMARY KEY,
name varchar(255) NOT NULL,
country_id integer NOT NULL REFERENCES country (id),
coordinate varchar(255) NOT NULL
);
I can't really say if name is appropriate - what are you planning on storing there? Please note that addresses are incredibly varied in international settings. Storing country is likely okay. coordinate is almost certainly wrong; if postgreSQL has a geo extension/module, use the relevant types - otherwise, use a pair of doubles/reals, and store latitude/longitude.
-- Source Table
CREATE TABLE source (
id SERIAL PRIMARY KEY,
name varchar(60) NOT NULL
);
... source of what?
-- Payment Method Table
CREATE TABLE payment_method (
id SERIAL PRIMARY KEY,
name varchar(60) NOT NULL
);
You may need to internationalize this one, as well. I'm assuming this is effectively an Enum (eg, 'VISA', 'MASTERCARD', etc).
-- Payment Table
CREATE TABLE payment (
-- Generic
id SERIAL PRIMARY KEY,
ip varchar(255),
email varchar(255) NOT NULL,
amount varchar(255),
payment_method_id integer NOT NULL REFERENCES payment_method (id),
-- Credit Card
card_name varchar(255),
card_expiration varchar(255),
-- Paypal
paypal_email varchar(255),
paypal_transactionid varchar(255)
);
email suggests that you should have some sort of 'payee' table, either as a person, business or... company. Also, why do you have two emails in the table? Try to avoid storing multiple logical 'entities' in the same table. While I applaud not having a column for 'credit card number', if you're going to be able to track reversals, you're going to have to know what number to reverse (assuming that you accept non-PayPal transactions).
Even under IPV6, you don't need an ip address that long - the maximum address is 39 characters (including delimiters).
amount should be either DECIMAL or NUMERIC (at least you didn't make it float/double/real...).
I'm also nervous about card_expiration being character-based. Really, what you have is an 'expires before business day' column (eg - it's usually expressed as 'expires 07/2012', which is 'active before 2012-08-01'), which probably ought to be stored as a date (probably without time, given the possibility of DST moving, but probably with timezone). Note that is still complicated, because, due to timezones (and ignoring any shifting business themselves do), there are 2 concurrent business (sun-has-come-up) days on the planet happening at once: when any particular bank/payment pre-processor switches is up to them.
-- Job Table
CREATE TABLE job (
-- Identification
id SERIAL PRIMARY KEY,
solrid varchar (10),
-- Job Information
title varchar(60) NOT NULL,
description varchar(255) NOT NULL,
truncated_description varchar(255) NOT NULL,
keyword_description varchar(255) NOT NULL,
how_to_apply varchar(255) NOT NULL,
website_url varchar(255) NOT NULL,
logo_url varchar(255),
page_url varchar(255) NOT NULL,
-- Dates
date timestamp NOT NULL,
expires timestamp NOT NULL,
-- Linked Tables
company_id integer NOT NULL REFERENCES company (id),
source_id integer NOT NULL REFERENCES source (id),
location_id integer NOT NULL REFERENCES location (id),
payment_id integer NOT NULL REFERENCES payment (id),
-- Status Flags
active boolean DEFAULT FALSE,
premium boolean DEFAULT FALSE,
indexed boolean DEFAULT FALSE,
error boolean DEFAULT FALSE,
-- Email Flags
email_reminder boolean DEFAULT FALSE,
email_confirmation boolean DEFAULT FALSE
);
What's solrid? There's no explanation.
You may want to create some sort of website table that you can just fk-reference, including things like the site and logo there.
truncated_description isn't any shorter than description... is one supposed to be shorter?
You are probably going to want a (internationalized?) tag (and job_tag) table, taking the place of keyword_description; split user search terms, get all matching tags, and find the jobs with the highest number of matches (and potentially search the regular description as well).
What date does date represent? postedOn? paidForOn? fufilledOn? Always try to name columns descriptively as to what they logically represent; these will often not include the column type.
expires probably shouldn't be a timestamp type. If you have it, it should be a date (sans time) type. The reasoning is that it's an imprecise, business-related concept (actual close of business isn't necessarily midnight...). This may apply to date as well, but the name is too ambiguous to be able to tell. Date ranges should also (pretty much always) be upper-bound exclusive (this blog mostly deals with SQL Server-specific problems, but the concepts are equivalent); in light of that, the name should probably be changed to something like activeUntil (or store the duration instead).
Is active derived information - can I tell just by whether the current (business) day is between date and expires? If so, unless you have an actual performance issue, it's best to not store it (ie what happens if active is true after expires is in the past?).
-- Advert Table
CREATE TABLE advert (
-- Identification
id SERIAL PRIMARY KEY,
-- Content
title varchar(60) NOT NULL,
description varchar(255) NOT NULL,
page_url varchar(255) NOT NULL,
-- Dates
date timestamp NOT NULL,
expires timestamp NOT NULL,
-- Email Flags
email_reminder boolean DEFAULT FALSE,
email_confirmation boolean DEFAULT FALSE
-- Linked Tables
source_id integer NOT NULL REFERENCES source (id),
payment_id integer NOT NULL REFERENCES payment (id),
-- Status Flag
active boolean DEFAULT FALSE,
error boolean DEFAULT FALSE
);
See previous remarks about date, expires and active.
Hmm, you may want some sort of 'reminder' and 'confirmation' table sets.
All I can think of at the moment. Providing expected (example) data contents may also help us critique your efforts.
