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

 

[dhookom]

In addition to Lilliabeth's criteria of:

If you are confident that will never happen, then you're OK

I would add that "and you will never need to add more gateway dates".

I realize this might be a personal development preference but I would still:
1. Create a gateway dates table
2. use autonumbers as primary keys
3. put people in a people table

I agree, this is an interesting thread and I doubt we can reach any kind of consensus.

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]

 

[markdt]

Well, thnaks for all your advice. I suppose i should ask the question to management about the gate fields and see if they want this kinda detail included. They currently use this system as is, but im just redesigning it for use with ASP.

Bob, thanks for reply. You helped me clear a few things up. Im going to take your advice and leave it pretty much the same with exception to the gate review fields.

Ive got round the id fields by using an appropriate select statement on me asp pages. Works perfectly.

And for anyone who's trying to insert blank date fields into a db using a sql insert statement. Dont bother. This set me back two days of searching on the internet trying different surgestions, which none of them worked. I ended up building my sql statement depending on if the fields were empty or not.

 

[dhookom]

You couldn't insert zero-length-strings into a date field but inserting Null should work. This does require coding an insert statement.

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]

Thanks BobRodes your DoB, DoD explanation does cover the ground that I was trying to explain.

And No fneily ( as far as I understand it ) the Taoist, Buddhist and Hindus - believe that the 'soul' ( Western term again - but you get my meaning ) has repeated existances but the physical moral flesh is a single use shell which therefore has a only one DOB & DOD.

and bear in mind that all I said at the beginning of this thread was

Maybe not lespaul.

.. .. I can see a good reason for seeing this as normalised

"Horses for courses" and similar comments apply.




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

 

[BobRodes]

<It's much more "readable" to have primary keys mean something.

That's only so if something other than the computer is doing the reading. Personally, I do my best to avoid presenting primary keys to the user wherever possible. If, say, for search reasons, an entity must have a human readable ID, I include that as a field, but not as a key field. Partly a matter of preference, though--I prefer the hassle you describe (numbers in no real definable sequence) to the hassles that derive from human friendly keys.

<Thus a need for a JUNCTION table which dictates multi-field primary keys as I believe this example needs.

While I agree that Associative entities (the "official" term for your "Junction" table, a table that resolves M2M relationships) are a requirement in most databases, I do NOT agree, and my experience does not suggest, that this "dictates" the use of compound keys. That's certainly one way to do it, but NOT the only way. I prefer to make a single Identity (i. e. autonumber) key as the primary key. So, typically, my associative tables have the identity key, and two foreign keys, one for each kernel table. In other words, I CAN avoid the use of a compound key here, and I do so.

Now, I can hear you disliking this already! But, I point out that your dislike neither creates a "need" nor "dictates" the way that things should be done. My solution works fine, tried and tested in the rigors of production.

<2/3's of the world population - Taoist, Buddhist and Hindus - have more then one DOB, DOD

Well, belief isn't the operating factor here, as you seem to (albeit facetiously) imply. After all, a leaf does not have to believe in photosynthesis to be green.....

<And No fneily ( as far as I understand it ) the Taoist, Buddhist and Hindus - believe that the 'soul' ( Western term again - but you get my meaning ) has repeated existances but the physical moral flesh is a single use shell which therefore has a only one DOB & DOD.

So, again we come back to context and semantics.....



Bob

 

[fneily]

1) REINCARNATION!!???!!!
2) Not more readable for the USER, but for any Access neophytes that may join the development group.
3) Yes, you can make a primary key for the junction (associative) table. Sometimes it's a neccessity. Basing my analysis on the works of the father of relational databases, Edgar F. Codd, for a simple construction, you'd then be creating unneeded fields. By theory, you'd migrate to the least amount of fields.
You're method works. So instead of using your car to get to work you use a wagon. After all, it works.
4) Why do people insist on using "happy faces"?

"You mean my whole fallacy’s wrong?" -- McCullum

 

[BobRodes]

1. Yeah? So?
2. You say potahto, I say potato...
3. If it's sometimes a necessity, why are you using the "reductio ad absurdum" technique to refute it? Also, what if my wagon runs faster than your car? (Dare I say "then my Sharke eats your Codd for breakfast"?) ~~~~~ (eschewing the happy face in favor of the raspberry, in the interest of communication...hey, I do my best to fit in)
4. Because it's just one big happy world we live in.

<"You mean my whole fallacy’s wrong?" -- McCullum
Outstanding. :-

 

[LittleSmudge]

By theory, you'd migrate to the least amount of fields.

I rest my case !


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