Tables for a Register...

[tassiegal]

Hi, the ever helpful VbaInet suggested that I post here for some advice about a database I am setting up. I foolishly didn't map the thing out before I started, so am now trying to pull it all together.
I am trying to set up a research register for our clinical school.
I currently have the following tables (from memory, I am currently on my mac and not at work, so can't pull up the DB to check):-

Researcher:- this is a one column list of all the discipline heads/heads of school/other staff. I am thinking it should probably be first name / last name but for some reason I did it as whole name in one column.

Location:- the list of locations

Discipline:- A list of the disciplines that our researchers work in. I DID have this as two separate tables as both surgery and medicine have sub disciplines, but I deleted the sub discipline table today as it was just causing me too much confusion. Everything is now stored in the Discipline table - so there is Emergency Medicine, and General Medicine as opposed having just Medicine and then calling up the sub discipline table to choose emergency or General.

Project Info:- Name, Description, location, Investigators, Discipline, Student type, Length and started.

The last table is the important one. There can be more than one investigator but only one discipline. Student type allows us to search for projects for students (i.e. summer, honours etc). Length is either < 6m, <12 months or >12 months. Started is a yes/no box.

Essentially I want a switchboard at the front with two buttons Add and search.
In the Add section the person can add an investigator, or a project. The project addition form is essentially drop down boxes except for Name and description.
The Search switchboard will generate a report based on a query (which is what VbaInet was helping me with).

I guess my question is do you think my table structure is decent, or should I start again from scratch mapping it out properly? Mind you I need to have this done by the 19th of Jan if at all possible.

 

[Privateer]

tassiegal:
Are you asking for someone to build you a switchboard? I just use a form and tell Access to open it when the database opens. I also build a reports form to handle the multiple options that people need to filter their results. You have less than two weeks, let me know if you want to discuss this?
My suggestions would be to normalize the project info table more. By that I mean break out the Investigators into another table. I would probably also break out the Location, Discipline and Student type into separate tables. Each table will have an autonumber field as primary key and the project info table will store these numbers.
Now you mentioned that there could be more than one investigator so there needs to be an intermediary table storing the unique ID from the project info table with the investigator ID number.
Databases work best when you store the numbers rather than the text and index these fields for quick searches. To get those numbers in there you use the combo boxes, where the first column is the number but you select and display the text. Let me know how this works out.
Cheers,
Privateer

 

[tassiegal]

nope - I can do switchboards! I just want suggestions as to if the tables seem adequate for what I want to do.
Investigators = Researchers tables- forgot to mention that so I have done that already.
Location is a separate table as is discipline. The only one that isn't is student type - which I currently have as a drop down box in the data entry form.
The deadline is not set in stone, its just thats when my boss gets back and he kind of said he would like to see it finished (along with the paper we are writing, so I am hoping someone finds me a tardis in the next day or two!).
Thanks for your advice!