Design of Database 2

[RAxel]

Hey all, I'm throwing ideas around for the design of this database and I just wanted to see what people thought here. I should keep track of clients (cities), their employees, and their quarterly payroll information.

I was thinking of 3 to 4 tables.

Table1
Clients
ClientID
ClientName

Table2
Employees
EmployeeID
Name
Social

Table3
QuarterlyInformation
EmployeeID
GrossPayroll
Quarter
Year

Now, my questions are these: 1) Should I have a table for each clients' employees? (IE, one for New York, one for SanFrancisco, etc...) Or just one big table of employees where you can only tell where they work based off of ClientID? The same goes for QuarterlyInformation, one big table or separate tables for each employee?

I haven't designed many databases so I'm not too experienced in designing for what could be future errors. I appreciate any input.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RAxel]

Sorry, got an error message when trying to view it.

"Error Occurred While Processing Request

 

[Lilliabeth]

Here is a tutorial re normalization:

http://www.phlonx.com/resources/nf3.php

One table for all employees. ClientId for foreign key. Some people would put all parties (organizations and persons) in a single table, but this model is significantly more complex, therefore not a good choice for a first project.

I can't tell what that quarterly info is, so no comment there, except to say certainly do not want to have seperate tables for each employee.

 

[RAxel]

Oops, I'm sorry. I meant a quarterly information table for each client (newyork, sanfrancisco, etc...) not employee.

So really, what you're saying is one table for client, one table for client's employees, and one table for quarterly information, correct?

Quarterly information is the information, such as gross payroll, that each employee makes in a quarter.

 

[PHV]

Sorry, got an error message when trying to view it.
Seems that Rudy's site has problem.
You may download this word version:

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Lilliabeth]

One table for clients. One table for all employees.

for the quarterly info - do not do a table for each client. ONe table.

It seems you have a desire to split tables horizontally without any need to do so. Be concerned about the width of the table, not the length. Do not repeat fields. Think about how hard it would be to query if I told you to bring me info for some guy named Joe. You want just ONE first_name field to query.

 

[RAxel]

Thanks for your help.

This is what I have:

Clients
ClientID PrimaryKey
ClientName

Employees
ClientID ForeignKey
EmployeeID PrimaryKey
LastName
FirstName
DateofBirth

QuarterlyInformation
EmployeeID ForeignKey
GrossPayroll
Quarter
Year

I think my last question is for the QuarterlyInformation Table. Should I have a new column as the primary key that just does autonumber? It's obviously related to the employees (through employeeID) but what should the primary key be?

Again, thanks for the help.

 

[Lilliabeth]

There are multiple right answers as everyone has their own opinion.

I am a fan of non-sensical PKs that are never displayed on forms or reports. If they are non-sensical and not displayed, the user can't get a notion to change them. For this reason, I would definitely make EmployeeID and ClientID autonumbers unless many users would be hitting the same table at once (doesn't sound at all likely in this app).

Personally, I would autonumber the third table, too. Some will disagree. I think PKs that have any meaning to the user lead to trouble.

I would set a compound unique index on combination of EmployeeID, Quarter, and Year to prevent some one from inadvertently entering the same info twice.

You might choose to (instead of having seperate fields for Qtr and Year) have a field for QuarterEndDate.

Some people would make combo of QuarterEndDate and Employee_ID the PK.

 

[RAxel]

One question I've always had is do all tables need a primary key? As you are describing non-sensical PKs which the user never touches, what is the point in even having a PK at all in that instance? If it's non-sensical, then wouldn't that mean it would be impossible to lookup?

Also, I have information already for the Employees Table and QuarterlyInformation Table in Excel, which I plan to upload to Access. Since this is the case, I would think I would have to assign the employees their IDs in Excel and then upload, which would negate my use of using Autonumber for employeeID, right?

 

[Lilliabeth]

Yes, if tables are joined, they need to have PKs.

Impossible to look up? Yep. That is not their purpose. They are present to enable relationships, period. Use codes, names, etc to look-up.

If it were my database, I would import data from Excel, add an autonumber PK that was never shown to the user.

But, certainly it is not WRONG to use a primary key that is actually data ("natural primary key"). It is a point of disagreement among designers. I happen to think that data should be stored in one and only one place. Notice you have EmployeeID in two tables. Autonumbers ("surrogate keys") are not information - they exist solely for joining purposes.

If you want to use the "natural PK", that is fine. But, yes, each table should have a PK.

If there is a chance - even a remote chance - that the system for assigning EmployeeIds will change someday, you really should use a surrogate key. If not, and EmployeeId 12 becomes EmployeeID ECR8021208, you will get rather nervous. It is possible to change values in a PK field, but it is no fun.

 

[RAxel]

I gave you a star because you've taken your time to answer my noobish questions.

I guess what I'm getting at is since I do have EmployeeID in two tables, doesn't that mean I must use a "natural PK" to relate them in a query, and thus must represent that in the excel files when I upload them to Access, right?

 

[Lilliabeth]

Thanks for the star.

Sorry, I don't understand the question. Are you telling me you already have foreign keys in your Excel ranges ready to go?

 

[Lilliabeth]

Keep in mind cool tools allow your user to choose a name or a code but in reality store an integer (the PK from the parent table stored in the foreign key field of the child table).

For example, you can create a drop-down on a form that will display names and codes of the employees, but when your user chooses one, really the Employee_ID is stored, even though it is not seen.

Want to designate that an Employee works for a particular company? Choose the company from a drop-down, but really (behind the scenes), the Client_ID is stored. Client_ID is not seen by the user.

 

[RAxel]

What I meant to say is that since I have data in Excel already for the tables, would it be best to assign PK's to the employees and upload them into Access like that?

 

[Lilliabeth]

I don't think it matters, but if you feel it would be easier than that is what you should do.