help with primary and foreign keys

[JamieY]

Hello, I am working on a database design for a project that I want to attempt. Right now, I am working on just the database. I am getting a little lost on primary and foreign keys. It's been a while since I have done this. Here is my list so far. The database is going to be for a web designer project management application I want to make using C# when I learn enough to make it. any help on this part of the database design would be great.

tblClients
ClientID(Primary key)
ClientName
CompanyName
Address
City
State
Zip

tblProjects
ClientID(PrimaryKey)
ProjectName
Durration

tblPayments
PaymentsID(ForeignKey)
MoneyIn
MoneyOut
Credits

tblNotes
NotesID(Foreign Key)
Website
FTPAddress
Username
Password
Notes

tblExpenses
categoriesID(Foreign Key)

I am thinking that the tblClients will need to be linked to all other tables. I thought this would be done from primary key to foreign key. Table payments will also need to be linked to tblClients and tblProjects. You get the idea?

[drjohn]

Does a client have many projects
does a client have many payments
does a client have many notes
does a client have many expenses???

OR
does a project have many payments
does a project have many notes
does a project have many expenses?

We can't tell with any great accuracy from here!

Why doesn't payments have a primary key but instead a foreign key?
Ditto notes, ditto expenses?

Also drop that tblPayments style of notation and just call the tables by the name of the ENTITY that they represent. And you don't need to call the id for the clients table clientsID, ID by itself would be enough.
eg compare your way
tblClients.ClientsID
with
clients.ID

less typing, less typos, easier to read.

so we need a bit more to go on to answer your questions.

[drjohn]

Also a problem is this bit

tblProjects
ClientID(PrimaryKey)
ProjectName
Durration

clients may possibly have more than one project, and clientID is VERY unlikely to be the primary key of projects.

a better structure would be client--< clientProject >---project

IE a linking table, clientProject, which had as its joint primary key, the PK from each of the other tables.

This approach might help you solve some of the other things I asked earlier, by illustrating how it should be done.

[JamieY]

Also a problem is this bit

tblProjects
ClientID(PrimaryKey)
ProjectName
Durration

clients may possibly have more than one project, and clientID is VERY unlikely to be the primary key of projects.

a better structure would be client--< clientProject >---project

IE a linking table, clientProject, which had as its joint primary key, the PK from each of the other tables.

This approach might help you solve some of the other things I asked earlier, by illustrating how it should be done.

Okay, I may be getting this all wrong. I am not sure I am following you guys. The techniques I was tought a while back may be old. I am trying to design this database properly. I am new to databases and this will be my first. So any information you guys can provide will be greatly appreciated. So you are saying not to use

tblClients

but instead to do

Clients.ID as the name of the table and also to show the primary key?

The next thing is that I thought the primary key linked to the foreign key so that when that link is established, it shows that the table can get information from the linked table.

So yes, a client can have many project, payments, notes and expenses. I don't want to limit to just anything to just one.

Can you give me a better example on how you would start? Maybe rewrite my tables in the first post the right way? the way you would do it?

[asaukani]

based on assumption :
one clients can have many projects
expenses is type of payments
payments depends on projects and expenses

i suggest to design database:
Clien: Id, Name, address and so on with Id is primary key no foreign key
Projects : Id, ClientId, and so on with Id is primary key and ClientId foreign key linked to Client table
Notes: Id, ProjectId, ClientId, and so on with Id is Primary, ProjectId is foreign linked to Project and ClientId is foreign linked to Client
Expenses: Id, Type with Id is primary
Payments : Id, ProjectId, ClientId, ExpensesId, In, Out and so on with Id is primary, ProjectId is foreign linked to Project, ClientId is foreign linked to Client and ExpensesId is foreign linked to Expenses
Just warning: it is better to put timestamp to most table

hope it will help

[JamieY]

Yes, that does help. thinking in terms of Access 2007, I am more use to using an actual word for primary key rather than just ID.

[drjohn]

There is no need to have ProjectId AND ClientId, in the notes table, you just need projectID, and then join project to client. Otherwise you risk having the WRONG clientID in notes for the project listed. The correct clientID is defined by the projectID itself.

Similarly for the payments table you would not need clientId, you would use a join, for the same reason.

Re using ID instead of a word - ID is a word - a little word, but it's a word.
Adding tbl in front of the name of a table serves no purpose, that's why I say don't use that antique naming style. Similarly, in its own table, you don't need to add the table name to the ID, as it can only be that table's ID. For a foreign key id, it is common to add the table name to indicate which table the foreign key came from and distinguish it from the tbale's own ID

[JamieY]

Ok, I attached a word file with my pseudo code for the database. I went with the form ClientID to represent the table name and the ID as the primary key. It will tell me what tables to make when it comes time for that. And then there is the foreign keys to link each table to the correct tables. Can you take a look and tell me what you think?

I took a Access class about 6 months ago this was the form that the instructor taught. Is this an old form or just user preference?