Jump to content

Re-designing an existing database


This topic is 4712 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I was hired by an international fellowship grant program to 1) consolidate all of its grants/grantee records 2) locate current contact information for each grantees/alumni member, and 3)create and print an alumni directory listing alumni grant profile and current contact information.

At the time, I had a very tight deadline to do all this and a limited knowledge of FMP, I had last worked with FMP when it was at version 7 and mostly entered data. Although, I managed to complete my duties in the allotted time, I am now interested in re-building this current database into something that is more sustainable and efficient and can, 1) be accessible and easy to use by other staff members 2) have the capabilities to publish some of our records on the web in the future, like an alumni directory 3) efficiently produce multi-dimensional reports drawing from different aspects of our information, and 4) have an email interface set in place that will be used to communicate with alumni members when updating their profile and disseminating announcements. All of which I've been able to do with my current set-up, which is a single-table database, but now that I am little more familiar with FMP, I would like put relational tables and functions into good use.

And, I've come here to ask for any advice you might have as I attempt to sort of re-build the database I already have.

Here is what I've been planning so far.

The tables I think I need:

1. Grants - We have about 5 types of awards, which vary based on academic track, location and recipient's level of education. The main point in having a table for just awards would be in being able to serialize them, which would be great way to keep better track of how many awards have been given out as we do have multiple-grant recipients.

2. Grantees - Name, educational background, award profile, current contact information and biographical data. I would also like to serialize these individual records.

3. Hosts - This includes schools and organizations which hosts grantees every year. This table would be useful when needing to know how many grantees they've hosted, list of names, for how long, what department have participated and making notes of special problems or circumstances that have come up in the past.

4. Administrative - This table would keep track of how many alumni we've located, how many remain lost. I am still having difficulty in determining whether or not I need this table. I think the advantage would be in being able to create reports based the status of alumni members (i.e. active, retired, deceased, unknown) or by decade, or having a place to document alumni publication or degrees. Perhaps all of this could be down in the Grantee table and an administrative table would not be necessary.

I know one of my main problems will be actually breaking down the information to create all the tables I have in mind. Right now I have a bit over 2,000 records/alumni and in each record I list their grant(s), host information for each grant, contact information, including biographical and admin notes. I have over 300 different field.

Anyone know what may be the best way to re-organize this information to make the transition as smooth and "neat" as possible? Is exporting to csv, serializing in Excel and then bringing it back to FMP my best option?

Would anybody be willing to send screenshots of a database that might be similar to what I have in mind?

I really look forward to receiving any feedback I can get. I am a novice FMP user, but I'm very enthusiastic about experimenting and learning more. Please let me know if I need to clarify anything. I am also using lynda.com for some guidance, but I definitely need more specific examples and solutions.

I've attached a diagram of how I think this database will work.

ER Diagram.pdf

Link to comment
Share on other sites

The most important thing missing from your description and ERD is the type of relationships between entities: one-to-many, many-to-many, or one-to-one. As a rule of thumb, toy should not have any one-to-one relationships, and every many-to-many relationship should be resolved into two (or more) one-to-many relationships by using a join table.

3. Hosts - This includes schools and organizations which hosts grantees every year.

But your ERD shows Hosts related to Grants, not to Grantees?? In any case, this is a good example of a many-to-many relationship, which should be resolved to something like:

Hosts -< Visits >- Grantees

4. Administrative - This table would keep track of how many alumni we've located, how many remain lost.

Do you mean alumni that have received grants (i.e grantees)? Apart from a few rare exceptions, a table that summarizes another table is redundant.

Link to comment
Share on other sites

Hello,

Thank you for your reply, I thought over it more carefully and think two one-to-many relationship tables might be better. Some Grantees actually have more than one grant (up to 6) and some are hosted by more than one institutions during a single grant period.

I did away with the Administrative table, the purpose I had in mind for it was to keep track of staff members' progress in locating alumni contact information. I have now realized that this information could easily go into the Grantee table. I have re-attached an updated ERD, your feedback is much appreciated, especially if you can give me some ideas on how I can re-organize my data which is currently in a one one-to-one table (so one record has the fields: grant 1, grant 2, grant 3, host Institution 1, host Institution 2, and so on) to the set-up I have in mind.

Again, thank you for your assistance.

Lisette

ER Diagram2.pdf

Link to comment
Share on other sites

Thank you for your assistance. I'm glad that somebody is helping me through this. I am having difficulty understanding your ERD, but I will try to explain the scenario a little better.

John Doe received a teaching fellowship (known to us as the ETA grant) in 1995, he was placed in ABC University is Seoul (host 1) during the first-half of his grant and at DEF University in Busan (host 2)in the second-half. Mr. Doe returned to Korea as a research fellow (known to us as the Jr. Researcher grant) in 2002, he was hosted by the ABC Reunification Association (a single host).

My program awards more than a hundred grants each program year and host institutions usually re-host different grantees each cycle.

It would be great if we could keep track of each grant so that they are easier to keep count of and also efficiently create reports to use in our annual reports and program profiles. I plan to serialize each award instance with a unique ID, like: 1995ETA_JDoe and 2002JR_JDoe, which would link to the Grantee table. The records in the Grant table would also include the program year, start and end date of grant, and host institution which would be linked to the host table.

The Grantee table would profile each grant recipient with a list of the grants they have received, (which would also display the program year, host institution, field of research or study), in addition to contact information and educational background information and other vital information.

The Host table would allow us to isolate the host and have a sort of profile of their affiliation with us, which would show who they hosted and when.

I hope this clarifies the situation a little better. I look forward to hearing from you again!

Lisette

Link to comment
Share on other sites

Let me try to translate your example to records in the tables of my ERD. Note that I have chosen arbitrary ID numbers to make the example easier to follow:

Grants:

GrantID: 1

Grant: ETA

GrantID: 2

Grant: Jr. Researcher

Grantees:

GranteeID: 100

Grantee: John Doe

Awards:

AwardID: 234

GrantID: 1

GranteeID: 100

Year: 1995

AwardID: 789

GrantID: 2

GranteeID: 100

Year: 2002

Hosts:

HostID: 24

Host: ABC University

HostID: 36

Host: DEF University

HostID: 48

Host: ABC Reunification Association

Sojourns:

SojournID: 1003

AwardID: 158

HostID: 24

SojournID: 1004

AwardID: 158

HostID: 36

SojournID: 2461

AwardID: 962

HostID: 48

For the Sojourns table, it may be more convenient to use GrantID AND GranteeID instead of AwardID.

I plan to serialize each award instance with a unique ID, like: 1995ETA_JDoe and 2002JR_JDoe, which would link to the Grantee table.

It would be best to serialize the awards - as well as any other entity - by using meaningless auto-generated serial numbers. This way your relationships will not break when someone's name changes, and there will be no conflict between "John Doe" and "Jane Doe".

Link to comment
Share on other sites

Hello,

I think I'm starting to get it. I'm just wondering what the purpose behind having an Awards and Sojourn table. Couldn't the year go in the Grant table? And what does the Sojourn table accomplish? I was just under the impression that the less tables/relationships we have the better. I know that there must be some really good reasons for this arrangements, so I'm really looking forward to your response. This has been such a great learning experience and I appreciate your help.

Lisette

Link to comment
Share on other sites

the less tables/relationships we have the better

As long as it's no less than what's needed...

I'm just wondering what the purpose behind having an Awards and Sojourn table. Couldn't the year go in the Grant table?

The Grants table is a table of grant types (e.g. "The Nobel Peace Prize" or "Best Performance by an Actress in a Supporting Role"). A grant can be awarded many times (e.g. every year) - therefore the year is an attribute of the award, not of the grant.

A grantee can have multiple sojourns (with different hosts) related to the same award. This is a one-to-many relationship, and therefore two tables.

Link to comment
Share on other sites

So if I have a total of 5 grant types, would I only have 5 records in the Grants table? Keeping in mind that more than 10 awards for each grant types are made each year. And I guess that the Awards table would be the place that would summarize how many grants for each type were awarded each year? I had never thought about it in that way.

Lisette

Link to comment
Share on other sites

Yes to both questions. If your grant types do not change, you could use a custom value list instead of the Grants table - but there will be a lot of work if that assumption ever fails.

I should also qualify my earlier note:

For the Sojourns table, it may be more convenient to use GrantID AND GranteeID instead of AwardID.

This is only true if a person cannot receive the same type of grant more than once.

Link to comment
Share on other sites

Now that I have a good idea of the kind to tables I will have and what their relationships will be, I would much appreciate you suggestions on how I can go about restructuring my data to fit this new schema.

I currently have a flat table (Grantee Table) and many fields. You can input grant information for up to 6 grants in each record. I have 2,000 records (by grantee) and 484 fields.

What my information looks like now:

Record for:

John Doe

Grant 1

Grant Type 1: ETA

Program Year 1: 1995

Research or Field 1: English Education

Host Institution 1: ABC University

Host City 1: Seoul

Host Institution 1b: DEF University

Host City 1b: Busan

Grant 2

Grant Type 2: Jr. Researcher

Program Year2: 2002

Research or Field 2: Sociology

Host Institution 2: ABC Reunification Association

Host City 2: Seoul

Grant 3

...

Following is the re-organizing process I am considering:

1.

Assign serial IDs to each record in FMP

Example:

John Doe

RecordID: 6895

2.

Export records to Excel in clusters by fields

Example:

Generate an Excel file of information found in the fields RecordID, Grant Type 1, Grant Type 2, Grant Type 3, etc.

3.

Duplicate the RecordID column to appear in every other column, then copy and paste by column set (RecordID and Grant Type 2; RecordID and Grant Type 2, etc), to run down the A and B columns

4.

Import these modified Excel files into the Grants Table.

5. Repeat as such with other data sets to accurately populate Awards Table and Hosts Table

I would much appreciate your feedback on my solution and of course please feel free to share your thoughts on a better/more efficient solution.

Lisette

Link to comment
Share on other sites

Assuming there is only one record per grantee in the flat table (i.e. no grantee has more than 6 awards), you could:

1. Assign a serial ID (i.e. GranteeID) to each record in the flat table;

2. Do 6 imports from the flat table into the Awards table, each importing the GranteeID and one set of fields out of the 6 available.

That would take care, more or less, of the Awards table. You then repeat the process importing twice from Awards into Sojourns.

Finally, you need to populate the Grants and the Hosts tables with unique records only, number them, and exchange the names in the other tables with IDs.

Link to comment
Share on other sites

  • 1 month later...

Hello,

I've been slowly working toward the final product of this database. I came across a possible glitch in our plan. What if one grantee has two host institutions during one grant period/award/sojourn.

For example:

Grant Type : Jr. Researcher

Program Year: 2002

Research or Field : Sociology

Host Institution 1: ABC Reunification Association

Host Institution 2: DEF School of International Studies

Host City1: Seoul

Host City2: Busan

Seems like it would be a many to many relationship. Do you suggest a second table for Host Institutions?

Thanks in advance.

Lisette

Link to comment
Share on other sites

  • 2 weeks later...

Please let me know if I should create a new thread for the following inquiry:

Is it possible to link records to each other in the same table. As mentioned before my organization awards grants and we have had people who are related to each other and have received grants that are not necessarily awarded the same year or have the same host affiliation. I want to have a field that references any relatives, if any, in grantee records.

If Suzy Q received a teaching grant in 2000 and her husband Johnny Q received a research grant in 2003. Is it possible to have their relationship show up on each of their respective records. This would be very efficient as they more than likely share the same contact information. I know that I could easily create the field, "related to" and enter the granteeID and just search by ID to get details, but is their a way to link the records based on noted familial relationships and have the relative information show up on the other's record and vice versa?

Thanks.

Link to comment
Share on other sites

This topic is 4712 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.