MySQL Forums
Forum List  »  Newbie

Re: Database Schema
Posted by: Rick James
Date: February 06, 2014 11:58PM

> `TitleAbrev` VARCHAR(5) NULL,
> `StateAbbreviation` VARCHAR(3) NULL,

If she already has short unique names for each Title and state, then use it for the PRIMARY KEY and toss Id.

> `StateAbbreviation` VARCHAR(3) NULL,

In the US, there is a standard set of 2-char abbrevs, so
`StateAbbreviation` CHAR(2) NULL CHARACTER SET ascii,
Change NULL to NOT NULL if appropriate.

> `ISOAbbreviation` VARCHAR(2) NOT NULL,

CHAR(2) CHARACTER SET ascii

Furthermore, INT takes 4 bytes; CHAR(2) ascii takes only 2.

> `Postcode` VARCHAR(4) NULL,

What country has postcodes that short?
CHARACTER SET ascii -- is probably appropriate.

> `Deactivate` TINYINT(1) NULL,

Generally it is 'wrong' to make flags NULLable. Instead use 0/1 (or whatever) for the values.

> `MedicalCentres_idMedicalCentre`

Names like this clumsy, not useful.

> `Injuries` BLOB NULL,

What are you putting in here? Pictures? Perhaps you meant TEXT?

> `DOB` DATETIME NULL,
> `Medications` BLOB NULL,

You are storing sensitive information. If this computer is stolen or hacked into, you will have hell to pay.

Go through all the NULL fields, and decide whether you will actually use NULL. Make the rest NOT NULL.

> `VisitPrice` DECIMAL(2) NULL,

Round units? Max of 99? Might the prices go up? Always in the same currency?

> `VisitDate` DATETIME NULL,
> `VisitTime` DATETIME NULL,

A DATETIME contains both a date and a time! Generally, it is best to have one field for Date and Time, not two.

> `Date` DATETIME NULL,
> `FromTime` DATETIME NULL,
> `ToTime` DATETIME NULL,

Ok, this gets a bit tricky. If you stick with the three fields, they should be
`Date` DATE NOT NULL,
`FromTime` TIME NOT NULL,
`ToTime` TIME NOT NULL,

> `idUserLevel` INT NOT NULL AUTO_INCREMENT,
> `UserLevel` INT NULL,

UserLevel smells like a "natural" PRIMARY KEY. Generally, it is better to use the "natural PK" when it exists and is available in the schema. (Hmmm... I keep saying that. Suggest you check all your tables.)

> VARCHAR(45)

Is everything really that short?

Sorry, I have not even gotten to reviewing whether the tables make sense.

Please provide some of the SELECTs you will have. That will help flesh out the JOINs and INDEXes.

Options: ReplyQuote


Subject
Written By
Posted
February 03, 2014 03:10AM
February 04, 2014 10:23PM
February 05, 2014 02:28AM
February 05, 2014 03:50AM
Re: Database Schema
February 06, 2014 11:58PM
February 08, 2014 12:40AM
February 09, 2014 08:10PM
February 10, 2014 03:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.