Designing a Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Designing a Database

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-15 : 21:40:01

I need to set up several tables for my new project.

I need to capture the following information:

Trainer's (person's name)

Training Times (4 slots a day - 8:00am, 9:00am, 1:00pm, 2:00pm)

Location of training (where classes are being held - Class Room A, B, or C

Managers Names (they will schedule training)

A table to capture some of the data to use as a report

This is what I've come up with is this ok?

Trainer Table

TrainerID PK, int

TrainerEmail varchar(75)

FName varchar(50)

LName varchar(50)

TrainingTimes Table

TimeID PK, int

Times varchar(10)

Location Table

LocID PK, int

Location varchar (25)

Management Table

MgrID, PK int

Management varchar(50)

TrainingInfo Table

TrainingInfoID PK int

FName FK varchar(50)

LName FK varchar(50)

TimesID FK, int
LocID FK, int

MgrID FK int – do I need in here?

beniaminus
Starting Member

21 Posts

Posted - 2010-07-16 : 03:55:30

You need the MgrID only if its a requirement to link a manager to a training record.
Also I wouldn't have the FName and LName as FKs. You want to put the TrainerID as a FK, then you can link the two tables.

If you want to look more into database design, look up Normal Forms.

Ben :)

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-16 : 06:42:17

Great thank you!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-16 : 10:43:46

I'm back...

There can't be any more than 20 - 25 ppl in a training class.

Which table should I place the MaxPPL field in?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-16 : 11:00:41

Okay I placed MaxNumAva in the TrainingTimes table. This way when a user selects the class room I can do a count to show how many seats are left.

The TrainingTimes table fields are:

TimeID
Times
MaxNumAva

Is that correct?

beniaminus
Starting Member

21 Posts

Posted - 2010-07-16 : 11:31:16

Well it would work. Are you just designing a database? Or is there going to be a front end to it (like a website or something)?

Ben :)

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-16 : 11:57:49

Yes there will be a front end to it as well. So I figured I could do some type of calculations to show how many seats are available for that time. So you are saying this is a good spot to place it?

Should I place it in the TrainingInfo table as well?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-17 : 05:45:39

Only place it in one spot - and then reference via Join if required

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

beniaminus
Starting Member

21 Posts

Posted - 2010-07-17 : 07:04:49

Jack is quite right.

The idea when designing a database is to completely eliminate duplication of data. So if each room can hold a different maximum number of people then store it with the room (or location, as you have referred to it). Or, if each training course has a maximum number of attendees then store it with the training course.

I'm not saying that duplication of data is completely forbidden, but if you find yourself doing it, it would be worth thinking if it's necessary.

If you like, you're quite welcome to email me your specification and design so I can glance over it for you.

Ben :)

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-18 : 21:11:55

Thanks so much beniaminus here it is...

The training room will have a max # of 25 per class so I put that field into the Training Times Table.

I will have text boxes for Fname and Lname

A dropdown for training times
A dropdown for location
A dropdown for Mgr

Submit button

My thing is how do I add the max # of ppl to the design. Should I have something that keeps a counter on how many seats are left on the front end or would I do that on the backend?

beniaminus
Starting Member

21 Posts

Posted - 2010-07-19 : 03:36:47

You wouldn't have to keep a counter. Before you add and attendee you can do a check to make sure the training session isn't full. This can be done using the count() function.

Ben :)

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-19 : 06:52:08

Okay so I would count to see how many seats are taken then send a message to the user how many seats are left?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-19 : 06:58:49

Here's more what I need to do for the project...

I need to display to the Manager the availability of slots to help the Manager in the scheduling process. The screen will need to show what slots are open and how many slots are available in each.

So I would need stored procedures to show the availability for each time slot on each date in each location. I will also need to create lists of attendees for each slot.

beniaminus
Starting Member

21 Posts

Posted - 2010-07-19 : 08:00:29

You'd just need one sp to show the availiablity. You can pass the time slot, date and location the the sp. List if attendees can also be done in an sp.

In regards to seeing how many seats are taken: The count() function returns the number of records in result set. So you'd have to write a query that returns the people attending a specific session, but instead of selecting column names you would write SELECT COUNT(*).

Ben :)

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-19 : 08:01:00

As an approach - think of breaking the application into layers: i.e data layer, presentation layer. Initially you could focus on database design , which will reflect business needs , broken down into database objects\ebtities. Then think about creating every stored procedure that will deal with data manipulation: of the type: SELECT,UPDATE,DELETE.
Then presentation layer i.e retrieving data,formating data , displaying data

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-19 : 08:25:46

Thanks for your replies. I was just told my database is not normalized enough.

They want me to combine all of these tables below into one I don't need the Manager's tbl. I also need to show training slots. How could I do that?

Trainer Table

TrainerID PK, int

TrainerEmail varchar(75)

FName varchar(50)

LName varchar(50)

TrainingTimes Table

TimeID PK, int

Times varchar(10)

Location Table

LocID PK, int

Location varchar (25)

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-19 : 08:38:56

Okay this is how I'm combining the tables:

New Table called Training

TrainerID PK, int
Fname varchar (50)
Lname varchar (50)
Component varchar(10)
Location varchar (10)
Times varchar (10)
RoomSize char(2)
TrainDate datetime

Is this correct?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-19 : 09:03:46

Okay I think I got it, here are the two tables I will use:

Training Table
Location
TrainDate
Times
MaxRoomSize

TrainInfo Table
Fname
Lname
Component
Mgr
Location
TrainDate
Times

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources