0

Database Design

Is this a bad design for a relational database. I don't see anyone doing examples that look like this.

But considering that an interview is comprised of all the different tables I have linked to the interview table it seems valid.

Except of OH Number (Oral History Number.) An oral history from one narrator may be comprised of different interviews conducted at different dates. Each individual interview is assigned a unique ID that make op a series that is assigned 1 OH Number.

I'm also thinking of putting "Interviewer, Indexer, and Transcriptionist in the same table.

Shawn
  • 1
  • 5
  • Would you break down what all objects are involved? Is your scenario that you have an interview and an interview is made up of one or more people, and said people have one or more different titles? If this is the case then I would have an interview object with an InterviewId and maybe Place field and Time field; a person object with PersonId and JobTitle and Name etc. Overall, if you could break it down more in simplest terms, that would help us and help you achieve the desired results. – HappyCoding Feb 16 '16 at 16:37
  • I wouldn't create tables like `Narrator` and `Interviewer` because if you ever have to add a new position, then you'll have to add a new table rather than adding a new entry to the Person table. – HappyCoding Feb 16 '16 at 16:40
  • Thank you for your advice. I was thinking of a person table as you suggested. Yes, an interview may have more than one person involved. However, that isn't usually the case. But it could potentially happen. And yes the person being interviewed may have had more than one title, or department. – Shawn Feb 16 '16 at 16:45
  • I want the database to be robust enough that if a researcher called in and wanted all the interviews conducted by John Doe, on Race Relations, I could pull a query for it. Just trying to think of every possible scenario that a researcher might request interviews under. – Shawn Feb 16 '16 at 16:54
  • Basically, the Oral History Department conducts interviews, and has about 1,800 interviews that arent in a relational database at all. Just two really big table in Microsoft Access. I am a student trying to build a new database for them using Filemaker Pro. Most of the values that are in the table I took from the database in access and am trying to find the most robust way to represent all the data that would allow for complex informational queries from researchers. – Shawn Feb 16 '16 at 16:56
  • So what's making this more complicated is I have information from 1815 records that I need to import over from Access. Most of that information is under the Narrator and the Interview tables. – Shawn Feb 16 '16 at 17:06
  • I noticed you said you're using Filemaker Pro to build this database. Since you're probably on SO to get a software engineer's/developer's help, I would suggest reconsider using Filemaker Pro. My personal recommendation is MS SQL Server. Here's a link of people listing pros and cons of Filemaker Pro and other widely accepted technologies: [What Are the Pros and Cons of Filemaker?](http://stackoverflow.com/questions/421960/what-are-the-pros-and-cons-of-filemaker) – HappyCoding Feb 16 '16 at 21:06

2 Answers2

0

I created the following mock-up for you given the details you have provided. I believe this will be a good starting place. You have an interview object and a person object. You have a joining table of InterviewPerson. This allows you to have one to many person objects per interview.

I want the database to be robust enough that if a researcher called in and wanted all the interviews conducted by John Doe, on Race Relations, I could pull a query for it.

To do the aforementioned as you have stated, you would join both the Interview table and the Person table on the InterviewPerson table, and then you would limit your query of that joining based on the Person.firstName, Person.lastName, Interview.topic (or title).

Please note, this is a rough draft but should be a good general idea and start.

enter image description here

HappyCoding
  • 641
  • 16
  • 36
  • 1
    Thank you HappyCoding, I think I want to join the Interviewer, Indexer, and Transcriptionist tables into a (Person) or (Staff) table. However, it feels like the Narrator table should be separated from the people who work here or worked on processing the interview? Again, just because I have over 1800 records from different narrators and the people who processed the interview after it was conducted numbers in the low hundreds only. – Shawn Feb 16 '16 at 17:13
  • If you wouldn't mind giving a little feedback on that idea. And thanks again. – Shawn Feb 16 '16 at 17:19
0

Database Design Redux

This is what I came up with based on your suggestions.

Shawn
  • 1
  • 5