Good relational database design

[cassandra.flowers]

I'm designing a database because I have to do it for the preperation work
for my A-Level ICT exam.

The database is for a building company. It has to store information on
building projects (tblProject) and employees (tblPersonnel) then link them
together.

The entities and attributes I have designed for my database are as follows:

tblProjectNumber (ProjectNumber)

tblProject (ProjectID, ProjectNumber, Stage, Foreman, TeamMember1,
TeamMember2, TeamMember3, TeamMember4, StageStartDate, StageEndDate)

tblStage (Stage)

tblPersonnel (StaffNumber, Name, Address, TelNumber, JobFunction ExpLevel,
StartDate, LeaveDate, GrossSalary, TaxCode, SickPay, HolidayEntitlement,
Qualifications, CourseAttended)

The database only needs to be in first normal form. So my question is, is
my design 'ok' as a relational database design? Am i allowed to have only
one attribute in an entity?

Thanks

Cass

[Jeremy Wallace]

Cass,

The first three normal forms all sort of blur in my mind, but there are some
repeating fields in here, which I would eliminate. Since you've said this is
homework, I won't suggest a fix, but I will offer some good reading:
Fundamentals of Relational Database Design. It's a short article by Paul
Litwin that I've posted (with permission) in the Developers' section of my
website.

Jeremy

--
Jeremy Wallace
AlphaBet City Dataworks
http://www.ABCDataworks.com
"cassandra.flowers" <ca***************@btopenworld.com> wrote in message
news:c0**********@sparta.btinternet.com...

I'm designing a database because I have to do it for the preperation work
for my A-Level ICT exam.

The database is for a building company. It has to store information on
building projects (tblProject) and employees (tblPersonnel) then link them
together.

The entities and attributes I have designed for my database are as follows:
tblProjectNumber (ProjectNumber)

tblProject (ProjectID, ProjectNumber, Stage, Foreman, TeamMember1,
TeamMember2, TeamMember3, TeamMember4, StageStartDate, StageEndDate)

tblStage (Stage)

tblPersonnel (StaffNumber, Name, Address, TelNumber, JobFunction ExpLevel,
StartDate, LeaveDate, GrossSalary, TaxCode, SickPay, HolidayEntitlement,
Qualifications, CourseAttended)

The database only needs to be in first normal form. So my question is, is
my design 'ok' as a relational database design? Am i allowed to have only
one attribute in an entity?

Thanks

Cass

[Bernard Peek]

In message <c0**********@sparta.btinternet.com>, cassandra.flowers
<ca***************@btopenworld.com> writes

The database is for a building company. It has to store information on
building projects (tblProject) and employees (tblPersonnel) then link them
together.

The entities and attributes I have designed for my database are as follows:

tblProjectNumber (ProjectNumber)

tblProject (ProjectID, ProjectNumber, Stage, Foreman, TeamMember1,
TeamMember2, TeamMember3, TeamMember4, StageStartDate, StageEndDate)
This is possibly valid but unusual. Hint: do you know for sure and
certain that a project will never have more than four team members?

tblStage (Stage)

tblPersonnel (StaffNumber, Name, Address, TelNumber, JobFunction ExpLevel,
StartDate, LeaveDate, GrossSalary, TaxCode, SickPay, HolidayEntitlement,
Qualifications, CourseAttended)

The database only needs to be in first normal form. So my question is, is
my design 'ok' as a relational database design? Am i allowed to have only
one attribute in an entity?

It's allowed but unusual. It appears that the ProjectNumber and the
ProjectID have exactly the same function. If so, choose one of them and
put it into tblProject. Why do you think you need both?

It would be more usual to create an entity like

Stage(StageID, StageDescription)

Where the StageID is a brief code and the StageDescription is a longer
text description. There is a 1:1 relationship between ID and description
and descriptions are always unique. In theory you don't need the ID
field at all, but there are practical problems in using long, free-text
fields as keys.

I suggest that you redraft the design and repost it, explain what
changes you have made and why. For a lot of people explaining something
to someone else is the best way of learning it.


--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[Steven McFerrin]

"cassandra.flowers" <ca***************@btopenworld.com> wrote in message
news:c0**********@sparta.btinternet.com...

I'm designing a database because I have to do it for the preperation work
for my A-Level ICT exam.

The database is for a building company. It has to store information on
building projects (tblProject) and employees (tblPersonnel) then link them
together.

The entities and attributes I have designed for my database are as follows:
tblProjectNumber (ProjectNumber)

tblProject (ProjectID, ProjectNumber, Stage, Foreman, TeamMember1,
TeamMember2, TeamMember3, TeamMember4, StageStartDate, StageEndDate)
tblStage (Stage)

tblPersonnel (StaffNumber, Name, Address, TelNumber, JobFunction ExpLevel,
StartDate, LeaveDate, GrossSalary, TaxCode, SickPay, HolidayEntitlement,
Qualifications, CourseAttended)

The database only needs to be in first normal form. So my question is, is
my design 'ok' as a relational database design? Am i allowed to have only
one attribute in an entity?

Thanks

Cass

Cass,

I believe the structure you have described is too cumbersome. I worked with
a similar structure when I first began working with dBase ten years ago and
it was . . . well, . . . impossible. The developer had originally set up
fifteen separate "account" fields as part of a check-writing database.
Unfortunately there were rare instances where a check had to be coded to
sixteen accounts. Too bad, so sad! Another problem existed if I wanted to
report on all entries to a particular account. Since the account number
could appear in any of the fifteen fields, each field had to be queried for
the account number.

I believe a more flexible solution would be to remove the TeamMember fields
from tblProject and set up a table that relates tblProjects with
tblPersonnel. .For example

tblProjectAssignments(ProjectID, StageID, EmployeeID, EmpProjStartDate,
EmpProjEndDate, EmpProjTitle)

allows for an almost unlimited number of employees to be assigned to a
project, it allows each employee to simultaneously work on a number of
different projects and/or project stages, and it allows an employee to act
in different capacities during the lifetime of the project. And the beauty
of the relational database is that you simply have to link the related
tables (tblProject, tblePersonnel, and tblProjectAssignments) and query on
the EmployeeID field to determne the projects that an employee worked on for
any given period of time and the capacities in which he served.

Is it okay to have only one attribute to an entity? I don't really know. I
have used a table with one field when I wanted to limit the values that
could be entered into the field of another table. It just seemed easier
than maintaining program code.

Steve