relational tables access db HELP!!

[markdt]

Hi, Ive been set the task of creating a project tracker database with a web front end. I have the following tables but i dont think there correct. Any help/advice would be greatly appreciated.

tblSalesman
SalesmanID:Autonumber
Salesman

tblManager
ManagerID:Autonumber
Manager

tblType
TypeID:Autonumber
Type

tblSector
SectorID:Autonumber
Sector

tblSite
SiteID:Autonumber
Site

tblProject
ProjectID:Autonumber
SalesmanID:number
SectorID:number
ManagerID:number
SiteID:number
CustomerName
CustomerLocation
VehicleProject
Comments
Status
TypeID
A0
A1
A2
A3
A4
A5
A6
A3Due
Quoted
OrderRec

The smaller tables are all linked to tblProject via 1 to many relationships. But im thinkin this probably aint the best way to do it. I have designed an ASP page that insert data into tblProject but im stuck on the id fields as they are numbers. And i want to enter text which refers to things like salesman, sector, site. A bit confusing i know.

Help please?

Cheers

Mark

 

[lespaul]

what are the A0, A1, etc. fields? have you read the document link below?

How are salesman and managers different? Do you store different information about them? Do you have a customer table? I would expect a customerID FK and a table that has information about the customer.

If your statement

i want to enter text which refers to things like salesman, sector, site.

means that you want to be able to search for a specific salesman by name, that is done via joins (Understanding SQL Joins)

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[markdt]

Hi,

The quote you refer to means when im entering a project using asp, i want to be able to enter salesman name, manager, sector, type which are all text. In the table tblProject i have these in as number because they link to other tables via the fk.

The A0, A1, A2 etc refer to dates at which a project is currently at.

Thanks for your reply

Mark

 

[lespaul]

usually you provide a list of possible choices of salesman. In Access or other programming languages, the user gets a drop down list of possible selections, however the dataset that is populating the drop down list references the foriegn key. When the user selects the text, the database is updated with the foreign key.

The A0, A1, A2 etc refer to dates at which a project is currently at.

This breaks normalization rules and should be changed.

Have you read the Fundamentals document?

 

[LittleSmudge]

Quote:
The A0, A1, A2 etc refer to dates at which a project is currently at.

This breaks normalization rules and should be changed.

Maybe not lespaul.

If A0, A1, A2 etc are 'gateway review dates' then I can see a good reason for seeing this as normalised.

It is just a matter of 6 parameters of the project that need to be stored. It just happens that all of these parameters are dates.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[fneily]

tblType, tblSector, tblSite actually looks like you OVER normalized. Probably unintentionally since I agree with LesPaul you should bone up on Normalization. Those tables aren't needed. They only have a single entry, thus why use an ID? Just use the actual value. eg. tblRegion with East, West, South, North. Forget the table, just use the actual directions. Also, you use Autonumber in all your tables. There's many a discussion in these forums about the drawbacks to Autonumber. My suggestion is to never use them. Makes life alot simpler.
Salesman and Managers may be placed in one table, depending on the info kept on them. Then you can do a self-referencing query to get employees bosses.

 

[fneily]

If A0, A1, A2 etc are 'gateway review dates'" - then have a field called Gateway Review Date. If you drop the A, then you have repeating column headings which is against Normalization. What happens when they to add 1 or more new "review dates" and only some records use them? Then you have variable length records which is again against the first step of Normalization. Dates as column headings are in general a bad idea.

 

[LittleSmudge]

No, NO NO

If the fields are what I think they mean then there is no possibility of ever having an A7 - unless you totally redesign the entire project management process. In which case the database will be one of the least of your problems.
No project will ever progress to the next gateway without passing the previous one.


It is common in Project Management terms to abreviate the Gateways to A0 - A6. But in reality they might be better called
A0 = Intial Launch Review
A1 = Prototype Developement
A2 = Hard Tool Commitment Review
A3 = Design Validation
A4 = Process Validation
A5 = Production Readiness Review
A6 = Post Production Review


They are a series of autonomous values, of a strictly limited number, that just happen to all be date data types.

Would you be more comfortable seeing the field list as
tblProject
ProjectID:Autonumber
SalesmanID:number
SectorID:number
ManagerID:number
SiteID:number
CustomerName
CustomerLocation
VehicleProject
Comments
Status
TypeID
ILR
PD
HTCR
DV
PV
PRR
PPR
DVDue
Quoted
OrderRec

It is no different in Normalisation terms - it is just using letters instead of the Gateway names that appear in the company's project management documentation.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[fneily]

I don't like your design that was presented in Design Validation. So let's have a Design Validaton 2 date. What happens now to your table??
I realize that A0, etc. aren't actual names. But they do represent Dates - ie. date1, date2, date3, etc. DATE, DATE, DATE, etc. Repeating column headings. Plus some projects may not need another Design Validation 2 date then you have variable length recors.

 

[LittleSmudge]

fneily,

No project will EVER need a Design Validation 2 date.

This is getting into heavy Project Management theory rather than RDB theory here.

You take the project to a DV gateway.
If it passes then the date goes in the field.

If it does not pass then it is sent away and the project manager has to complete the actions and resolve the problems and then represent to the SAME gateway.


Think of it like recording TIMES that you pass toll booths on a motorway.
You get to A0 and pay the toll and log your time.
You get to A1 and pay the toll and log your time.
You get to A2 and pay the toll and log your time.

If you get to A3 and find you don't have the money you have to go somewhere to get the cash to pay the fee. Then you get to return to the booth and pay, log time and progress.
Your action does not suddenly increase the number of booths along the motorway and it doesn't rename the booth either - it is just that you APPROACHED it twice, but succeded in getting through ONCE. - Thats why it is referred to as a "Gateway".




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[fneily]

Project Management theory!!!??? Did you know that every human being has some iron oxide in the front of their brain. So IN THEORY we know where the North Pole is just like birds. I wonder what markdt is doing.

 

[dhookom]

Just to add my 2 cents regarding normalization... I would not have hard-coded fields for dates. Each date should have its own record in a related table. This would allow for much greater flexibility.

With the existing structure, what would happen if you wanted to record additional informat about the gateway such as:
- Expected Completion
- who was responsible for the completion
- when was the gateway date updated
- comments about the gateway status

IMHO normalizing the structure is the preferred method for handling the gateway dates.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LittleSmudge]

Okay Duane - but all you've done now is increased the complexity beyond the original spec. Adding time and cost beyond what the customer really wants.

You have an additional table that can grow further, I'll grant you that. In the original table you have just changed A0 - A6 from Date types to Foreign Key types.

It doesn't address fneily's issue at all.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[markdt]

Thanks for all your replies. Still not sure what to do with those id fields in the tblProject table. Should i put them all in one table or put manager, salesman, type, sector, site in a seperate table.

Mite be me just being dumb. I learnt normalization no more than 6 weeks ago. But designing this is giving me a real headache.

Thanks

 

[markdt]

What about this, would this be any better?

tblProject
ProjectID:Autonumber
Salesman:text
Manager:text
Sector:text
Site:text
Status:text
Type:text


tblCustomer
ProjectID:number (linked via 1 to 1)
CustomerName:text
CustomerLocation:text
VehicleProject:text
Comments:memo
A0:shortdate
A1:shortdate
A2:shortdate
A3:shortdate
A4:shortdate
A5:shortdate
A6:shortdate
A3Due:shortdate
Quoted:shortdate
OrderRec:shortdate

cheers

 

[dhookom]

In a relational application, you shouldn't need to store the text values for Salesman or Manager. I am working on a similar project at the moment that stores the ID number but displays the text using a <SELECT>.

My application has a main project table and then a task table. Each task record is related to a project. A task would probably be similar to you A0...A6 fields. However, I can add as many tasks as I want (or need). My task table contains fields like:
[tt][Blue]
TS_ID autonumber primarykey
TS_TITLE title of task
TS_OWN_ID ID of person responsible
TS_DESC Longer description of task
TS_BEGIN Date a task began
TS_EXPECT Date a task is expected to be completed
TS_CLOSE Date a task is closed
TS_PERCENT Percent complete
TS_JUSTIFY Justification for completion
TS_ST_ID links to tblStatus
TS_PI_ID links to tblPriority
[/Blue][/tt]

Users never see an ID value since <SELECT>s are used to display all pick-list information. I started with "Absolute Project Manager" found at planetsourcecode. There was much I didn't like about the original so I have made lots of modifications.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

I don't find much issue with creating an application that is flexible and expandable to meet future needs. It seems that every time I compromise normalization or other good development standards to "not go beyond an original spec", I get bitten.

It doesn't generally take much longer to create a solution the proper way. I invest in my clients' long term ability to grow and accomodate new functionality.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BobRodes]

In general, your table structure looks pretty sound to me, except I would take fniely's advice about putting salesman and manager in a single (perhaps employees) table, and having a "BossID" field that has the key for another employee in the same table. Furthermore, you may not need to have all those lookup ("characteristic entity") tables, but better to err on the side of caution as you have.

<If A0, A1, A2 etc.

Although I do agree with fniely on some things here, I don't agree with him on this, and I do agree with LittleSmudge. Here's another example of the same thing:

Name
DOB
DOD

First off, one could argue that this is in first normal form (1NF), and one could argue that it isn't. Here's the pro argument: I'm born once, I die once. As such, these fields are not in one to many relationship with the record, but are two dates independent of one another that have a one to one relationship with me. Therefore, their relationship with me is atomic. Here's the con argument: these are two "life occurrences". Since there are two of them, they are in one to many relationship with the record. Therefore, their relationship with the record is NOT atomic, and putting them in the same table violates 1NF.

So, you can see that the arguments all have to do with how you define the two dates. If birth and death are two different things, one way, if they are examples of the same thing, the other way. So, as is so often the case, it comes down to what's practical, and in most cases, it's practical to leave these in the one table.

So. It is NOT a violation of 1NF just to have several fields of the same type in a table. If each instance of the field type (date, in this case) has to do with a different aspect of the overall state of the container instance (i. e. the record), then this is fine. As another example, a series of yes/no flags would be analogous, and it's reasonable to put these flags in the record that they apply to, rather than creating another table of yes/no values with a foreign key. (That can be reasonable too, if, say, 2 out of 10 of the flags will be set and the rest will be null. However, it doesn't violate 1NF, or any other normal form, not to do so.)

The point is if an instance of an entity (i. e. a record) will pass through several states on its way to completion, and the date at which those states are achieved is part of the instance's state, that isn't really an attempt to cram a one-to-many relationship into one table, so it isn't really a violation of the first normal form. What WOULD be a violation of the first normal form would be if each of the dates were actually date stamps of multiple instances of the same occurrence, and the number of instances of that occurence would vary from record to record. I don't see this to be the case here.

As for AutoNumber, I really can't say. Perhaps there are implementational reasons (Access has plenty of weird problems, in my experience). However, I use the analogous concept regularly in industrial-strength SQL Server databases, and I find it the simplest way to handle primary keys. If fniely is arguing that a simple integer value primary key isn't the simplest way to key tables, I don't agree with that for sure. I avoid compound keys when I can.

HTH

Bob

 

[fneily]

Points well taken. I'll concede to your dates explaination (except 2/3's of the world population - Taoist, Buddhist and Hindus - have more then one DOB, DOD. A little Western prejudice??)
As for autonumber, besides not feeling in the holes when you delete records, if new people enter the group and must maintain the DB they'll face a one - many relationship that may look like this: 5 - 2, 11, 231, 78000. What does that mean? Doctor 5 to patients, part 5 to suppliers, etc. It's much more "readable" to have primary keys mean something.
"I avoid compound keys when I can." I've never been doing contract work for a company that a many - many relationship hasn't appeared. Thus a need for a JUNCTION table which dictates multi-field primary keys as I believe this example needs.

 

[Lilliabeth]

What an interesting threa!. I initially agreed with fneily - 7 date fields cannot be right.

Then, of course, I got a lesson in project management theory.

Then Duane posted this:

With the existing structure, what would happen if you wanted to record additional informat about the gateway such as:
- Expected Completion
- who was responsible for the completion
- when was the gateway date updated
- comments about the gateway status

And I realized that the real question is this:

Markdt, will you possibly ever want to record any fact about even one of the dates? If you think maybe so, then you need to redesign. If you are confident that will never happen, then you're OK (except you do not want two seperate tables for salespeople and managers).