0

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.

2
  • More details on what a Result is might help, but there is not enough detail to suggest a "best way". If you need a single Results table, you might look into a Supertype/Subtype structure for results. Or you could use a table with every column you might need and only select from the columns meaningful to the Result for a row type.
    – RLF
    Sep 2, 2013 at 17:13
  • I have edit the question and hope it has enough details now. Thanks
    – Bogdan
    Sep 3, 2013 at 6:36

2 Answers 2

2

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.

2
  • Thanks for the answer, something like this was thinking as the best solution. But i have some questions. When you say "all of these IDs are taken from the same sequence" you mean that I have to have ID unique to all the result tables and not per table? If so how can this be done? And also how can I search over all tables after without using a for loop, because I don't know in which table to look. Thanks again.
    – Bogdan
    Sep 3, 2013 at 7:51
  • Check the documentation of your DBMS if it provides sequences (most do). I would then create a trigger on each resulttype table to fetch the next value from the sequence and set it as the resultid on INSERT. As a safety measure, add a UNIQUE index over the resultid column in the ProjectResults table. The "resulttype" column in this table will tell you in which resulttable to look.
    – Twinkles
    Sep 3, 2013 at 12:13
0

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).

1
  • 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. I have edited the question trying to provide more details. Thanks.
    – Bogdan
    Sep 3, 2013 at 6:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.