Two dbs or One? (1 Viewer)

[SaraMegan]

Hi, all... I'd just like some simple advice.

I'm nearly finished a db for Human resources that lists all employees and all of their information. It has six tables (hopefully created correctly and normalized...)

Anyway, they also want something that will track applications for vacant positions. These positions can be applied for by in-house or out of house people, and could use several of the tables created for the other database, if not all of them.

Should I just add to my current database, or is it possible to link the two together and just look all the other stuff up? I figured that since I had two objectives, I might want two databases, but the second could draw so much information from the first.

I also have a query in my database which gives me a list of vacancies and all their relevant information, which will also be helpful to the second part.

Thanks, as always, for the input!

--Sara

 

[RichMorrison]

Sara,

From what you wrote, is sounds like you have ONE "mdb" with all your tables, queries, forms, etc.

If that's the case then you should split the one mdb into a front and and back end.

Once you do this then it is much easier to make new front end applications that use the same back end data.

HTH,
RichM

 

[The_Doc_Man]

There are a few issues to consider.

1. How big are the two databases? If they are relatively small, like measured in tens of megabytes or less, then size isn't an issue in the decision.

2. Do the two databases differ radically in their security issues? If you really, really, REALLY don't want folks seeing the employee data, then the best way to make sure no one sees it is to make sure it isn't part of the openings database that you DO want folks to see. This would be a reason for a separate database with links from employees DB to openings DB.

3. Do the two databases have different administrators? If so, then the employee DB admin and the openings DB admin probably don't want each other to go mucking about in each other's rice bowl. Also known as the "too many cooks" syndrome. This would be a reason to have two databases.

I'm sure there are other considerations, but these three are the most important that I can think of.

 

[SaraMegan]

The Doc Man,

1 - the two databases are pretty small. The first, which is almost complete, is right 734kb. The second, though it will be bigger, won't be too much bigger.

2 - Security issues are pretty much the same for both. The same people will be using both databases, and it will be limited to that group. (about 8-10 people, though it will probably only be used by 2 of them.)

3 - The Administrator is the same for both.

I'm thinking this is leading me to put them together in one db?

RichM,

Could you tell me more about splitting the db? What it does and what's involved?

Thank you both very much for your replies.

--Sara

 

[The_Doc_Man]

Sara, those two DBs are small enough that you could put them together. Given that security isn't an issue and rice-bowl politics isn't an issue, I would say it is OK to join them.

OK, here's a side-effect of that decision. You probably have added a couple of extra possible roles that might be different for folks using the open positions data vs. the folks using the employee data. ALWAYS think "groups" when doing this sort of thing. If the same person has to hit both subsets, put them in both groups. If not, put each person in the appropriate group.

Another issue: If you now have TWO data subsets in the same table, that makes it that much more valuable to your company. Which means regular maintenance like backing it up and repairing it and compressing it every so often, just because. (Because it is a good idea!)

I think you will find that the possibilities inherent in joining two data subsets like this will make it worth your while, particularly if you add something like a "Candidates" table for folks who have applied recently for a position. If you can figure out how to do it, you can maintain a "callback" list of recent candidates who didn't work out for position A but are a reasonable fit for new position B. That sort of touch would make your management sit up and notice. Of course, if you do that, you have to have rules on how long to keep that information around before it is considered stale. We do that for one of the government DBs we keep here.

Basically, the idea is that the more data you have to mine, the better the results will be when you go digging!

 

[RichMorrison]

Sara,

Splitting an MDB means creating a new MDB container for all your tables.

1) Make a new MDB and import all the tables from the original. Now you have 2 exact copies of the tables.
2) Go to the original and delete all the tables. Now you have one copy.
3) From the File/Get External Data menu select "Link". Find your new MDB and select all the tables.

The advantage of this is that you can make a new application that uses some but not all of the tables as you stated in the original post.

Since your first application is a success, they will always want more. Putting the data in one container and the forms, queries, reports, in another makes it easier to add on later.

RichM