database design for a literature review (help)

[luisd]

Hi,
After wasting a lot of time in excel I now recognize that I need to come up with a design for tables in a database that I'm trying to make for a literature review of biological correlates.

I'm reading Michael Groh's Access bible and trying to plan out everything on paper but I can't visualize a way of getting all the data into a normalized db.

At the moment I started with ~10 studies, each has tables of the variables they used, many of these repeat across different studies. Each variable is either continuous or discrete, and different authors treat the discrete ones in several ways (different categorization or different criteria).

from what I've learned I planned what the reports should look like and thought I should then make a form for inputing the data.

One for data pertaining to each individual study (eg. region, animal studied, analysis type)

one for data about each individual variable (e.g. variable type, source, significance and somehow include which study has used it, but different studies use these variables in different ways so I don't know how to consider this multivalue issue)

I though writing this down in paragraph form should help but I am still confused. If anyone can help please chime in.

thanks
luis

 

[spikepl]

This is difficult for outsiders, since you are the one that can make decisions on "sameness" or lack thereof, depending also on how you ultimately desire to present you data and what processing, if any, you need to get done. The major point is, that you need to structure your data adequately enough to support what you need to produce.

So the question is what do you need to produce? What are common data items, what aren't ? And among the common - or similar - data, what are the specific exceptions? Something warranting an explanatory note at the bottom of a table, or something that would exclude it from the flock? Or some other exception, one or more?

I would categorise the data, find out what is required to describe each item and the possbile exceptions, and thereafter design some tables and logic to fit.

 

[Galaxiom]

It does sound very complex.

I think I can shed light on the discrete variables (if I understand what you mean by that term.) As an example I am considering the discrete variable where a count of samples falls into a measurement band.

Initially you might think of having fields (0To1), (1To2) etc and because each study has different band using a separate table for each measurement and study. Way too many tables and even more forms and very difficult to make comparative reports.

One thing all these discrete variables have in common that they have bands. It does not matter what the actual range of those bands are when you are storing the values. You just store the count of samples that fall into each band.

There are several variations on this theme.

In the main table the fields would be like:
StudyID, VariableID, BandID, SampleCount

The VariableType table:
VariableID, VariableName, VariableNotes etc

The parameters of the band are stored in a separate table, eg:
BandID, BandMax, BandMin, UnitID

Units:
UnitID, UnitName

Plan this schema very carefully and run it by us here before you move on. How you do this will make a massive difference to the complexity of the data entry and reporting.

Though this example might not specfically apply to your data I hope it gives you some ideas.

 

[luisd]

hey
thanks for the comments
I kind of understand the band tables but I can't seem to apply it to my data yet.
I'll keep reading my access books, and I've attached a spreadsheet of some of my data. It has a lot of redundant data, null values and it combines Data and Presentation, I started building it before deciding to use access properly.

If anyone can check it out and comment I'll be very grateful.

ld