Combine two databases - IDs & relationships

[MikeKD]

Hi folks,

I've got two separate databases.

1. has tables of pupils, classes, assessments, teachers & instruments etc and is used for day to day registering and grading kids in class and in ensembles.
   The pupil table has all the pupils in the school and the ID is their school ID which I have been assured is always unique.
2. Is flat. It's used to hold the results when I audition potential music scholars to give me a rough ranking order.
   Each record is one audition with its own UID. If the same kid auditions twice, that's two records.
   Not all the kids that audition come to the school.

Ideally I'd like to combine these two;

1. so that I can look back at current kids audition data easily.
2. so that if someone auditions twice I can see that.
3. so that it gets synced along with the rest of the database when that gets implemented.

My issues are;

1. I think this is a many (auditions) to one (pupil) relationship.  Each pupil could potentially have more than one audition, but an audition can't have more than one pupil.
2. When I audition the kids, the don't at that stage often have a school ID
3. Is there going to be much of a performance penalty on an iPad? The scholarship database runs quite happily as it is, will adding a database with another 16 tables slow it down as much as it sounds, or will the iPad just use the one table it needs for the scholarships?
4. Does it matter which way round I relate the auditions and pupils? - Do I put a portal on the pupils layout and select auditions to add from that? - Do I need a join table?
5. Once I've added the auditions table, is it easy to import the data from that database? Just import & match the correct fields?

Hope that all makes sense!!

Cheers,

Mike 

[keywords]

I've only just read your post, but since no one has replied in 13 days perhaps a few thoughts even at this late stage may assist your thinking.

 

1.   In the first part you say the the auditions db is flat—I assume that means it has just one table. Then in part three you ask about "adding a database with another 16 tables". My first thought was that you could simply add the auditions table to the larger db, but this bit confused me somewhat.

 

2.   Again, I was a little confused by your statement: "the ID is their school ID". I trust this simply means the ID that is used to identify them within the overall school db. You DO have a unique, reliable pupil ID field, right?

 

3.   If you do simply add the auditions table, then relating it to the pupils table via the pupil ID is the obvious way. However, this means that some of the people in the pupils table will not actually be pupils, but only potential pupils. You may thus need to consider adding a status field (candidate, pupil, graduate, etc), maybe changing the name of the pupils table to something more generic.

 

There are a few other possible issues to consider, but that will do for a start.

[MikeKD]

Thanks keywords, that does help.

 

The school ID should be unique - I've been using it as my UID so far.  As you've said, kids who audition aren't usually existing pupils at the school, so don't have a school ID allocated at this stage.

 

Therefore I need to generate a 2nd UID for these - I can't risk attempting to generate my own "schoolID" (the formula is quite simple - 1st 3 letters of surname and then three figure number) that is then replaced if / when they join the school.

 

Can I just put an auditions portal on a pupil table layout and link to that using my own secondary ID? - I don't need a join table as there will only ever be one pupil in an audition, right?

 

I need an "active" type field, not just for this situation, but so that I can keep records on pupils who've left.

 

Is it OK to have two UIDs in a record?

 

Many thanks!!

Mike