table Design Issue

Question

table Design Issue

simflex-897410

Hall of Fame

Points: 3881
More actions
February 8, 2010 at 1:01 pm

#386274

Greetings all,
I have 6 Excel forms that I will like to turn to db objects.
Let's call these forms Form1, Form2...Form6.
Each form has several but different values.
For instance, Form1 looks similar to the following:
IncidentType #ofWeekReported YearToDate
Office Fires 1 10
House Fires 2 28
Fire Alarms 0 177
...
...
Total Fire Incidents 3 2 1 5
M Svc Response 6 19
etc
etc
After all have been listed, you get grand total.
This part doesn't need to be on the db, I don't think.
Also, these 6 forms below to 4 different Groups.
Can you please help me suggest how I can design the DB?
I hope I gave enough information.
Thanks very, very much, in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Let me suggest you turn your thinking around.... to the question ... what data do you want out of the DB ... in the form of reports and /or someone running a simple select statement. Then show what data you want to include in the DB from your Excel books.

And then repost to this or a new Forum...

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

Thank you very much for your response.

I thought I did just that.

I attemped to show *how* the excel file looks like.

I would like the db to have similar layout.

The users can select/add/edit/update info.

If the db can mimick the excel file, that's precisely what we want.

Again, data on excel file looks like this:

IncidentType #ofWeekReported YearToDate

Office Fires 1 10

House Fires 2 28

Fire Alarms 0 177

......

Total Fire Incidents 3 2 1 5

M Svc Response 6 19

etc

The important thing there for me is to find a way to record sub groups and there sub totals.

For instance, the main groups here are IncidentType,#ofWeekReported,YearToDate

Then sub groups: office fires, house fires, fire Alarms and their sub total.

Then another subgroup begins, until all subgroups are record and then a grand total.

If I am still not giving you or any kind hearts willing to assist what you need, please let me know.

Once again, thanks a lot for your assistance.

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 3

See below for a guess. I have used an artificial key which some may disagree with.

All calculations, incidents of a particular type or subtype, incidents per week, incidents per year, etc. do not need to be in the db. Calculate this data when needed.

CREATE TABLE [dbo].[Incidents](

[IncidentID] [int] IDENTITY(1,1) NOT NULL,

[IncidentDate] [datetime] NOT NULL,

[IncidentType] [nchar](20) NOT NULL,

[IncidentSubType] [nchar](20) NULL

) ON [PRIMARY]

This assumes the db will hold every incident, that is, the primary data about each incident. If you db holds summary info then that is a different method.

Do the users enter every incident or do they enter, for example, the number of house fires every week?

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 4

I'll ramble a bit here so feel free to ignore if this doesn't apply to your situation.

If you are modeling after paper or excel forms then you are essentially saying the process is perfect and I just need to migrate this to a database. I would closely examine your process, this is your opportunity to change it, once it becomes realized in your database model, changes will be difficult.

If you store each and every incident you will be able to perform more robust analysis than if you are storing summary data.

If you are storing the number of office fires per week you will never be able to answer a question like, on what day of the week do the most office fires happen? or do house fires spike on holidays? etc...