Table Structure Question (GREEN)

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

Table Structure Question (GREEN)

Author

Topic

Descartes75
Starting Member

2 Posts

Posted - 2008-07-24 : 00:33:04

Hi All.
I am fairly new to database design. I have read through posts and know the basics of database design but am having a hard time putting it together. Help with the following will aid me a LOT:)
Just need a rough idea please.

Here is a simplified version:

Files can have 10 stages. They can go back and forth between these stages.
We record the file name, the stage it was at, the stage it went to, the date and the user that sent the file to that stage.

As an output, I need to be able to query:
- Which files are in specific stage?
-Which stage takes the longest (based on dates).
-Which user takes the longest?
-Given a stage and a user, return files.

Now, what would be a POSSIBLE table structure?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-24 : 00:47:00

[code]
Start with this

Id Int Identity
FileId Int
FromStageId Int
ToStageId Int
User Int
ChangedDt DateTime
ActiveYN Bit -- 1 for Present Stage remaining all are 0
[/code]

Descartes75
Starting Member

2 Posts

Posted - 2008-07-24 : 01:43:00

Thanks Peter.
But according to my studies, that is not a way to do this as there will be repeats of User, Document and Stage (Unless I am mistaken).

I think I am looking for more than one table, normalized or close to normalized so I can run Join table queries and get the information fast.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-24 : 02:00:11

But u stated that "They can go back and forth between these stages."
so maintain all the info in a single table and for getting desired info use self joins

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 02:22:40

with Peter's suggestion you would have more than 1 table (that will be why he said 'start with this')

the table he showed you would be to track the movement of files through stages. you'll notice it is basically a collection of ID's and a datetime. joined to that table you would also have a STAGE table holding details of the stages themselves, a FILE table with file name etc., and a USER table.

this means you're not 'repeating' the user info etc, you're just using the ID of existing users. make sense?

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources