-1

I want to create a table in a PostgreSQL database to store user data (Account info, contact info, geographical info). I have typed out the following SQL and I am wondering is it acceptable for PostgreSQL (in terms of best practices, data types, and lengths)?

CREATE TABLE users (
userID SERIAL,
username VARCHAR(255) NOT NULL,
password VARCHAR(60) NOT NULL,
email VARCHAR(255) NOT NULL,
active VARCHAR(255) NOT NULL,
lenderAcct BOOLEAN NOT NULL DEFAULT FALSE,
resetToken VARCHAR(255) DEFAULT NULL,
resetComplete VARCHAR(3) DEFAULT 'No',
CONSTRAINT users_pk PRIMARY KEY (userID),
firstName VARCHAR(20) NOT NULL,
middleName VARCHAR(20),
lastName VARCHAR(20) NOT NULL,
primaryPhone VARCHAR(50) NOT NULL,
primaryPhoneExt VARCHAR(10),
altPhone VARCHAR(50),
altPhoneExt VARCHAR(10),
fax VARCHAR(50),
legalAddress1 VARCHAR(25) NOT NULL,
legalAddress2 VARCHAR(25),
legalCity VARCHAR(25) NOT NULL,
legalState VARCHAR(25) NOT NULL,
legalZip VARCHAR(16) NOT NULL,
legalCountry VARCHAR(25) NOT NULL,
mailAddress1 VARCHAR(25) NOT NULL,
mailAddress2 VARCHAR(25),
mailCity VARCHAR(25) NOT NULL,
mailState VARCHAR(25) NOT NULL,
mailZip VARCHAR(16) NOT NULL,
mailCountry VARCHAR(25) NOT NULL
);
Jared
  • 81
  • 1
  • 2
  • 6
  • 1
    yes, acceptable, and can't say about `in terms of best practices, data types, and lengths` without `knowing your requirement`(what and how you are going to operate it). – A_Sk Apr 04 '15 at 18:57
  • Personally I suggest normalizing out the address information into an `address` entity, with an `address_type` field indicating whether it's a mailing address, legal address of residence, or secondary contact address. Same with phone contacts. Also, all that `varchar(255)` stuff is archaic; why 255? Are you using Pascal strings? Use `text` or plain `varchar` unless you specifically wish to limit the length. If you do, set a length limit you've thought about, not an arbitrary limit from old systems. – Craig Ringer Apr 05 '15 at 01:09
  • @CraigRinger - You brought up a very interesting tip, which is the address entity bit. I've never really gotten into using entity objects, but I am wondering how doing what you've suggested would help or hinder the performance of my database. Also, some of the columns in my database were inherited from an existing database, but also I don't profess to have a great handle on manually creating databases. – Jared Apr 08 '15 at 23:03
  • @Jared Focus on design correctness first, performance second. If you have performance issues you can selectively denormalize or add materialized views if/when required. Splitting things like addresses into side tables could help *or* hinder, depending on query patterns; probably a bit of both. – Craig Ringer Apr 09 '15 at 01:38

1 Answers1

0

The PostgreSQL side of the table looks ok. You could consider changing the varchar(x) types to text (check out this answer). The PRIMARY KEY can be introduced as userID SERIAL PRIMARY KEY, ... although this has no effect on the table structure.

Best practises cannot be commented on more without knowing your full table structure but those considerations are mostly not PostgreSQL specific anyway.

Community
  • 1
  • 1
Simo Kivistö
  • 4,247
  • 3
  • 38
  • 42