Simple or relational tables?

[kimberlin]

Several months ago when I started a new job with a small recruitment firm, one of the things I did was to create a series of simple tables so that I had a record of who was registered, what they did and where they lived along with a few other things. At the time there were only a couple of us in the office and I was the only one who could access the database so I just used the tables themselves to filter the information I needed at the time. Now there are more of us in the office and somebody decided to set up a network and share the database, when it hadn't even been split and I have found that as a result there are often occasions when the information that I've put into the various tables hasn't been saved as the others have had them open when I've been inputting.

Now I'm wondering if I should set up a relational database to keep the records in so that in effect I would make sure stuff could be split into the front and back end. I've come up with a set of relational tables (I've used the relationships manager to check it works) but the trouble is I know that not every client will have an entry in every field on every table, so will that make it more or less useless before I start feeding the data in to it, or would I be better off just moving the data to one simple table and creating forms and queries to go with it (something similar to the personal contacts template on the MS Office site springs to mind)?

 

[MarkK]

You say, among other things, that you want to keep track of who registered and what they did. The thing to notice here is that one person might do many things, and you can only represent that fact using related tables. There might be many records in some table B (activity) that belong to a single record in table A (person).
So it's not 'simple or related tables,' but rather, 'simple is related tables.'
Cheers,
Mark

 

[kimberlin]

Thanks for pointing that out Mark, I've been so bogged down in what the person's normal trade is that I forget that some of them are multiskilled and am now wondering if maybe I should put in an extra notes column so that they go down as multiskilled in the trades listing but I can say what levels of the different trades they can do in the notes! I hadn't thought about keeping notes on previous placements as the details for those will be in hard copy files but I think it might be a good idea to incorporate that as well from the present onward.

Another thing I've been thinking about this evening is that the same town or county name will arise frequently, so I'm wondering if maybe there should be separate tables for that part of the address detail?

So far I've got the following relational tables:

tblworker
ID
Last name
First names
Email address
Home phone
Mobile phone
Address
Town
County
Attachments

tbltrade
ID
workerID
Trade
CSCS

tblclient
ID
workerID
tradeID
Client
Start Date

tblpayrate
ID
pay rate

tblpayvia
ID
pay via

tblpersonal
ID
workerID
Birthdate
NI number
CIS
Registration Date
payrateID
payviaID.

I don't need any bank account details as they would be paid through another company.

 

[MarkK]

A table represents a type of thing. One row in a table represents a discrete instance of that type. One field in a row represents a single property or dimension of that instance.
Following that logic I would almost always expect Birthday and FirstName to be fields in the same table since they are properties of the same type of thing, typically a person. Your structure suggests a worker might have many birthdays.
From there, what are the types of objects you need to keep track of? Is a client a type of person? Could you have a person table that has an IsClient field and an IsWorker field?
What is a trade? In your design a worker can have many trades, but one trade cannot have many workers? Is that correct?
Your PayVia table makes sense if you have a finite list of items. Maybe you have Visa, MC, Disc, Cash, and so on in there, OK. Your PayRate table is suspicious though because I can show you an infinite number of rates. If the rate is truly variable then it is a dimension of something else and doesn't merit a table.
Hope this helps,
Mark

 

[kimberlin]

You're definitely giving me some food for thought here, Mark!

The PayVia table would have a finite list of items which would be the names of the various paying companies, which aren't the same as the names of the clients or firms which they are working for at the time. Most of the Pay Rates would be according to the type of job the workers were doing. They are usually paid a set rate by the hour but it could also be a set price for the whole task.

From what you're saying do I assume that most of the fields in the Personal table could be absorbed into the Worker table? I had originally kept them separate as apart from the RegistrationDate field we often don't get those details until they are due to start a work assignment and I thought if I had them in the Worker table that it would result in a lot of fields having empty records and I wasn't sure if that was allowable.

The trade or job type issue is a tricky one because some of the workers have more than one type of job that they can do, these are the ones who are multiskilled but as you say there are also individual trades that we have a lot of people registered for so I'll absorb that one into the Worker table and put notes in for those who can do more than one type of job eg they are fully qualified as an electrician but are also partially qualified carpenters so could take on either an electricians placement or an improver carpenter role. Would that then make sense?

The objects I need to keep track of are the clients (firms) currently employing which of our workers. I also need to keep track of what tradespeople I've got available for work in which town and county, as well as all those who are currently registered with us and what qualifications they have got to do the particular job that they are after. Hope that makes sense.

I won't be able to log back on again until this evening, so if there's a delay in my answering you'll know why!

 

[MarkK]

How often you use a field is not a consideration. If you have a table called tBox with fields height, width, depth and color, and you hardly ever use the color field, it doesn't matter. It is still a dimension of a Box, so that's where it belongs. Maybe it won't appear in all your reports if it's rarely used.

The issue of the trade information appears to be a many-to-many relationship. You have tWorker and tTrade tables and one worker might have many trades, but in the same way one trade might belong to many workers, so you need an intermediary table tWorkerTrade...

tWorker
WorkerID
FirstName
LastName

tTrade
TradeID
Trade

tWorkerTrade
WorkerTradeID
WorkerID
TradeID
SkillLevel

So tWorkerTrade connects those two objects. Now you can count how many trades a worker has and just as easily count how many workers a trade has. And see how SkillLevel inescapably belongs to the worker's relationship to his trade? So now you can keep track of how good everybody is at everything they do.
Maybe not simple, but elegant, and when you think about it, an accurate model of a particular reality.

And it looks like you have a few other things to integrate. A client or firm requires a table. How is that client related to your tWorker? And I see clients and workers and trades, but where are the jobs? Will that be a table?

Also, I have math that works on a sphere with GPS coordinates, so if a worker's location has GPS coordinates and various sites do, it's simple to calculate the length of a straight line between them. This won't always be the shortest drive, but a reasonable estimate. Then you can rank workers by trade, skill, distance and so on...

Cheers,
Mark

 

[kimberlin]

I suppose I could add a site location field into the client table and many thanks for pointing out about the two separate trade tables. I can see know that I need to amend the way I had set my initial trade table out, I was going to allow for all combinations of skill level such as fully qualified, improver or mate for each trade so I think I could probably change the field name for SkillLevel to AdditionalTrade and put the TradeID for that second trade straight into that field if that makes sense or would the table have a fit? I'm thinking that I could probably add extra fields for those who have more than two trades into that table as well. I thought the client would be related to the worker through the worker ID in a one to many relationship?

 

[MarkK]

I'm thinking that I could probably add extra fields for those who have more than two trades into that table as well.

No, this suggests that I've not explained well and/or you've not understood. The structure I proposed allows you to record multiple trades for each worker by adding a new record for each trade to the tWorkerTrade table. That table connects a worker to a trade. If there is such a connection then the worker has that trade. If not, not.

Imagine you have a distinct worker named Bob in table tWorker. His WorkerID is 12. Also, you have the distinct trades electrician (TradeID=4), carpenter (TradeID=3) and plumber (TradeID=12) in tTrade. Lets say Bob passes his plumber test, so way to go Bob! Now you want to represent this in your system...
You add a record to table tWorkerTrade to connect the Bob record to the Plumber record, so that the new tWorkerTrade record has data like ...

WorkerTradeID, WorkerID, TradeID, SkillLevel
           24        12       12    "Newbie"

So if bob goes back to school and becomes a carpenter too, you add this record to tWorkerTrade...

WorkerTradeID, WorkerID, TradeID, SkillLevel
           25        12        3    "Newbie"

So if a worker gets a new trade you don't edit the worker and you don't edit the trade, you add a connection between the two.
How many trades Bob has is...

SELECT Count(*) FROM tWorkerTrade WHERE WorkerID = 12

...which might be zero, but in Bob's case is two.
Does that makes more sense?
Cheers,

 

[kimberlin]

Thanks for explaining that Mark, I see where you are coming from now. I understand what result the code will tell me from using the count function in Excel but where would that code be typed in?

 

[MarkK]

That code's a query in SQL, which is sort of like that language that tables use. You can write stuff like that in the query designer.

 

[kimberlin]

Don't I need to have a SQL server to do that or will access just let me do that sort of thing anyway? So count will tell me how many trades that worker has but how would I get it to display the actual trade names?

 

[MarkK]

SQL is Structured Query Language and Access tables use a dialect of that language. No SQL Server required.
When you create a query it opens is a design mode, so you have a grid you can use to add fields and link tables and so on. Start there since it's easy to modify and test stuff.
Cheers,

 

[kimberlin]

Thanks for all your help Mark. Hopefully by the end of the coming week I'll have managed to sort out everything that I want to do with the new database!

 

[kimberlin]

So adjusting my table structure to the attached would be better?

 

[MarkK]

Yeah, that looks alright, but I'd put a bunch of test data in there. It's when you start putting data in there you find out if all your links are OK.
Cheers,
Mark

 

[kimberlin]

Ok, I'll do that later today. Thanks for the advice Mark.

 

[kimberlin]

I think I must have got something wrong en route or else I'm expecting too much of the tables. The first two records pulled into a query fine but the later ones haven't gone in despite telling it to refresh and I think I may need to alter the link between the worker and firm table as well because that could become a many to many relationship.