Re: Best Practice for Statistical Data Organization in databases

On 26.12.2006 22:04, Beth wrote:
I'm completely new to databases. What is the best practice for organizing statistical data in a database? I have 400+ parameters for 185 countries for 20+ years each. How many tables should I use and how should they be structured (e.g. countries in rows, years in columns, etc).

Difficult to tell without more detail. With what you disclosed I'd create a single table with columns (country, year, parm1, parm2, ... parm400). Reason: I assume all these parameters are different values. With this layout you can easily do calculations for one parameter through several years etc.

If the number of parameters changes and they all have the same type (numeric) then you could also do (country, year, parameter_name, parameter_value). This design is discouraged, there are recent threads about this concept (called "entity attribute value"), one of them is here:

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/e6ed468f44b5f495/3414fe0399e3540f

Kind regards

robert
.

Relevant Pages

  • Re: Basic application design
    ... simultaneously by users in all four countries. ... Database is in one country where the internet is excellent. ... If we go for an ASP net based solution, then the server hosting the ...
    (microsoft.public.dotnet.languages.vb)
  • Re: WHY
    ... as easily as you can put it in a spreadsheet? ... Rates are defined BETWEEN two countries. ... > countries to determine the respective exchange rate basis (and I hope ... > And this is something you can't directly translate to a database. ...
    (microsoft.public.excel)
  • Re: How many names and addresses can you have for ID card
    ... But a German database, or an American one, would be orders ... but not about other countries. ... you don't think a German or American-designed database would be ... If you associate with the wise, ...
    (uk.legal)
  • Re: How can I organize my database?
    ... in a relational database, you don't want to do this. ... > chance to put more countries and more regions for each agency at the same ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Its a filter but not a filter
    ... Your needs are best met if you treat your source data as a database and ... performa query against it. ... data from an Access database, but you can just as easily substitute your ... for the countries I'm interested in. ...
    (microsoft.public.excel.worksheet.functions)