Database design with multiple tables

database-design

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.

Best Answer

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.

Related Question