I have a data structure as follows:
Two main components: Projects and Results
So I have a table called Project which has multiple fields as Id, Name, Title, StarDate, EndDate and many more which correspond to a project.
And each project can have many Results. So first I thought to make a results table but there is a problem. I have many types of results (around 15) each of them not having too many common fields. So then I thought to make a separate table for each type of result but then it will be very hard to get all the results for a project because they can be in any of the result tables.
So to be more clear I have the following data:
For Project: ProjectID, UserID, Type, Title, Summary, StartDate, EndDate, Value, Website
For results I have the following types:
- Book: Title, Authors, Year, publisher, city, number_pages
- Book Chapter: [All the fields from book] + chapter title, number of pages for chapter, pagination
- Article: type, title, authors, magazine, ISSN, year, pagination
- Thesis: title, author, coordinator, year
- Citation: category, number of citations
- Events: type, name, period, members
- Brevets: author, name, holder, institution, number, type
- And five who have the exact same structure (Tehnology, methods, products...): name, authors
- And a distinct category: Others
The result is something that a user attaches to the application as a deliverable for a projects...what was resulted in that X project. And in the end the application must be able to select all results (regardless of their type) for a Project X or for a User X and display them, edit them...manage them.
So far I am thinking of:
- making one large table with all the fields (can come up to 30 columns)
- making a table for each type of result and another one for a master control which depending on the type says in which table to look
- make one small table for all the results and keep only project_id and a description column in which i save all the fields as XML or JSON
But none of these seems good to me and was wondering if there is something else acceptable an ok.
How can I structure the database for it to work in the best way.