Data Design Entity Relationships - help or suggestions on redundancy

Question

I'm struggling on the number of necessary Entity relationships I should consider or need and could use any 'willing suggestions' if I'm not on the right path. I'm a developer and don't do a whole lot with complex databases but did do some reading on data design.

I don't know if every Entity relationship is necessary? For example if I have a many to many relationship with a Question to Source, would I also have a many to many or even 1 to many for Question to Author, which the authors is also tied to the source. I think Questions would be setup like "topic" but I'm really just not sure due to lack of experience.

What I'm trying to do is best explained in the problem statement:

"How to create a centralized research database model that is capable of receiving multiple research reference notes based from multiple resources (e.g. books, video, quotes, audio, etc.), which can be cross-referenced on multiple topics or commonalities and searched on a variety of specific queries (e.g. topic, sources, dates, notes/comments, definitions, concepts etc)."

What I've done:

Below is an image of a matrix I'm using to organize and break out tables and relationships. Since this is a research database, it is based upon the APA source referencing style used in professional writing (light an dark orange fields). The "x"s indicate what of the APA fields, are required for particular reference sources. The blue fields are extraneous fields for categorizing and commenting.

The relationship and table notes are incomplete since I'm trying to determine relationships, what is valid and what I don't have right.

---

SV

Answer 1

Hi svMSDN,

In this scenario, I would use a fact table (customid - timeid - orderid - resourceid), a custom table, a resource table, a time table. Each table will have their id.

Topic, Source Type, Author are all fact of a book.

Best Regards,

Teige

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Thanks for the recommendation.

---

SV