Databse with a few 1 column tables. Is this ok?

FlyingMonkey

New member
Local time
Today, 01:47
Joined
May 1, 2014
Messages
8
Hi

First time here and I have a query about a Database I'm designing.

I'm designing a Database for my section in work. I have Normalised it already. The Database is to hold information about the recruitment of hospital workers. This is not a recruitment system. We already have one in the office. It's merely to keep track of our own section's work.

We recruit for hospitals all over the country and the reason why I have a few tables is because I'm using them as look up tables. I use tables rather than lists so anyone can go in and add another hospital to the list. I have tables for title of the post, employing authority and staff member who is dealing with it too. The main table will have information concerning the actual individual post, eg date of advertising, no of applicants etc. I don't want people to have to keep typing the hospital and title etc. as there is a lot of repeating in these.

I have no need for other information in these tables as it's purely for the lookup column. At the moment I have H01, H02 etc. as the primary key for the hospital table and similar for the others. But I don't think I need them as the hospital name will be unique.

Is this acceptable or am I going to cause trouble for myself in future? I can't think of another way of doing this.
 
Sometimes all you need in a table - especially one only used for lookups - is a primary key and a data field. In this case, the tables save space (the PK value that gets repeated in the linked table uses less space than an actual name), allows for more accuracy (no worrying about typos), and eases maintenance.

So yeah, you're probably fine.
 
Thank you.

The reason why I want to do it this way instead of using a code as Primary Key is that at the moment we're using Excel and it's what people are used to working on. They will want to see all the information on the form. If I used the PK in the main table would name (rather than the code) of the hospital etc. show up in the form?

I will be using a split form for data input so the bottom half will be similar to Excel but the top half will display all fields instead of having to scroll to the right.

One more question. Is a split Database a good idea for this? There will be around 10 people accessing the Database. Not everyone will be inputting data at the same time though and if they do they would be working on a different record to everyone else.

I want to use Access for this because of it's query/reporting functions. The amount of time wasted on people trying to work out stats based on date etc. is ridiculous.
 
1) For something like these tables, you generally use either a combo box or a list box on the form. The users only see the list of names - you keep the key itself hidden. The table, however, stores the key value, be it a number or an alphanumeric code. (For example: the table will store H01, but the user would see "Mercy Hospital" displayed in the control.) If you're not sure how to do that manually, the wizard will step you through it when you add a combo box to a form. This does make queries *slightly* more complex, but all you have to do to display the hospital name in a report or query is to include both tables in the query and output the specific fields you need. It's pretty easy in the query builder UI.

2)If there is any possibility of multiple people using the database at the same time, you definitely want to split the database. After splitting, make sure every user has a local copy of the front end - that's easier on the server and drastically reduces the risk of any sort of conflict.

3)Your tables should always have at least 2 columns: a primary key and at least one data field. If nothing else, that makes maintaining relationships much, much easier.

Edit:
Also, try to get out of the Excel mindset as much as you can. Setting up a relational database as if it were a spreadsheet really does make it harder on you down the road.
 
Thanks again.

I've just recently earned the MOS in Access through a Diploma course I'm doing, which included systems design. I'm still a bit of a novice though. Haven't implemented my Database yet. Want to make sure I have it right first.

I've had a few trial runs and I'll set it up as you suggest.

As I said I have normalised the data so I have taken the hospitals and job titles out of the main table as they will repeat and aren't reliant on the specific post. But I don't need to keep further information than that. I will just have the 2 columns, the code and name.

I've will also set the combo boxes up in a form rather than the table. I heard it's much better practice.
 
Yeah, you can't really set up combo boxes on a table. :-P

And as long as the code is always unique, it can be the primary key. There's no rule saying that a PK HAS to be an autonumber field.
 
In your situation, I would definitely use two columns. Hopsital names do change, and more frequently than you would expect. (St. Johns to Mercy in multiple cities. Local hospitals bought out by larger ones, etc.)

Sometimes I use a single column, but only when the "Key" will never change. However, I don't ever remember wishing I would have used a one column table when I used two, but I have wished I had used a two column table a time or two.
 
The only comment I might make is that your keys as H01, H02, H03 ... might be limiting whereas a true autonumber PK would be less limiting. It isn't that you couldn't do your DB with that type of key, but it is easy to write a limiting format of some type and suddenly find that your key field wasn't wide enough. Like, what do you do when you find that you need key H100? That's not a joke if you allocate fixed-size fields for a report.

That's a very VERY small quibble.
 
Thanks for your help everyone. I now have the combo boxes on the form instead of the table. Makes much more sense. Only the detail shows on the form and only the key shows on the table. My colleagues would be frightened if they saw the table :-)

I will be hiding the tables so everyone will have to work through forms.

I have replaced some of my own PK formats with Autonumbers. The reason why I originally had H01 etc. was to distinguish between the key in different tables but I presume this won't matter as any code would reference the table first?

The main table looks good now so I'm going to start designing some queries/forms.

Should I index (No duplicates) on the detail in my look up tables? Only the Job Title would need to have new records added regularly (for specialty posts) by staff members. I don't want them adding something which is already there. I want to make it as foolproof as possible and don't think they would check first everytime, even if I put it in the manual.
 
indexing on the detail in the lookups, as well as the key field makes sense. you don't want the same lookup appearing in that table multiple times.

comments like that make it clear that you understand how it all hangs together.

even adding a unique index doesn't prevent "near misses" though. The same hospital entered slightly differently, or misspelt. Managing data is not easy, at all.

eg

Derby Royal Infirmary
Derby RI
Derby R.I.
 
My colleagues would be frightened if they saw the table :-)

Yeah, a well-designed relational database looks kind of terrifying to people with no experience with them.

I will be hiding the tables so everyone will have to work through forms.

Very good call - end users should NEVER be working with the tables, only with forms.

I have replaced some of my own PK formats with Autonumbers. The reason why I originally had H01 etc. was to distinguish between the key in different tables but I presume this won't matter as any code would reference the table first?

Correct. Whether you access data via a query/SQL or directly with VBA, you'll always tell the engine which table contains the data in question.
 
Thanks again.

I've come up with another little problem.

I have switched my combo lookup to a query so that only current staff will show in the drop down box. I used a Yes/No data type to distinguish this.

However, when a staff member has left the section and I untick their box, their name no longer shows on the previous records they worked on.

I will probably need this for the hospital authorities as I think these will be changing name/areas soon but I will need to keep the old data in the table.

Is there a way of getting only current information to show in the drop down box for new entries but to keep the old information for our records?
 
I'm a relative newbie myself, but the information here might help, particularly the section on cascading.
 
Thanks again.

I've come up with another little problem.

I have switched my combo lookup to a query so that only current staff will show in the drop down box. I used a Yes/No data type to distinguish this.

However, when a staff member has left the section and I untick their box, their name no longer shows on the previous records they worked on.

I will probably need this for the hospital authorities as I think these will be changing name/areas soon but I will need to keep the old data in the table.

Is there a way of getting only current information to show in the drop down box for new entries but to keep the old information for our records?

I've solved this before by using rowsources that automatically change to apply a filter when the user clicks a combo box to edit it, and then change again after the edit is made. I would do this in the following way:

1. When you make the combo box, set the query up so it includes everyone, i.e. no filtering for whether they're currently employed staff or not

2. Put in the OnClick event on the combo box VBA code that runs something like

ComboBox.RowSource = "SELECT * FROM tblEmployees WHERE EmployedYN = True" ' this changes the box's row source
Combobox.Requery 'this updates the combobox to reflect the new rowsource

3. Put similar VBA in the AfterUpdate event

ComboBox.RowSource = "SELECT * FROM tblEmployees" ' this changes the box's row source back to the original
Combobox.Requery 'this updates the combobox again

This may look a little daunting if you're not used to VBA, but I'll post an example version when I have some time later on.
 

Users who are viewing this thread

Back
Top Bottom