Database design with multiple tables

Question

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

EDIT:

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.

Answer 1

Making one large table would be a mistake: you'll have a table with lots of NULL values and it would soon evolve into a maintenance nightmare. And about saving the data as XML or JSON -- (slaps your wrist) that's for even considering it.

Every Resulttype should get its own table, each of these tables has a primary key over an ID column and (this is the trick!) all of these IDs are taken from the same sequence!

Then you only need a table with the columns projectid, resultid (and optionally resulttype). Since you will look for the primary key, even searching over all tables can be done fast.

Answer 2

It depends on how you want to process your data later:

If you need to run queries like "get all book titles (for project x)" or "get all articles for a specific author between year 2000 and now" you need to put each value at least into a different field. You could create a project table and a result table for each result type. To map the result to a project you can add a project-ID field to each of your result table or you create a separate result table with project-ID, result-ID, result-Type.

If you don't need to query directly on your results you also could just serialize the result information and put it into one field of just one result table (project-ID, result-POS, serialized result).