PDA

View Full Version : Databases - need help with creating a schema



Vadi
January 1st, 2009, 09:46 PM
Hi all,

Does anyone have any tips/resources on database scheming? I need to create a DB that will store changing data about applications on a daily basis.

My first idea was to have a table for each app, then inside that for each day, and inside each day for the data of each day - however that was using the data model as data storage itself, which was a no-no. I can't figure out another way for this - a possible idea was to have a table for all apps, and one for all days, but I am unsure how to link them together.

Any help is appreciated.

davec64
January 1st, 2009, 09:52 PM
First off, sorry but I use MSSQL:(

But what about:

A series of Look up tables i.e.

Applications Table

Date Table etc

And then have your data table linked to your look ups by foreign keys, so the data table would have a row for each event you want to record referenced to an Application and a Date etc.

Just a thought!

I might have missed the mark completely though! :D

Vadi
January 1st, 2009, 09:56 PM
Didn't quite get your point, but wouldn't new tables need to be created for new dates?

pp.
January 1st, 2009, 09:56 PM
Let's say you wanted to record the size, the temperature and the colour of each application on a day to day basis.

That'd take exactly one table with exactly five columns:
- application id
- date
- size
- temperature
- colour

Vadi
January 1st, 2009, 10:14 PM
Let's say you wanted to record the size, the temperature and the colour of each application on a day to day basis.

That'd take exactly one table with exactly five columns:
- application id
- date
- size
- temperature
- colour

I don't see how would that work though. I'd need to know the size of a given application on a given day, temperature of a given app on a given day... this way wouldn't work that I see. Unless I'm missing something completely obvious?

pp.
January 1st, 2009, 10:22 PM
Date Application Colour Size Temperature
01.01.09 Evolution pink XL 45
01.01.09 Calc reddish XXL 20
02.01.09 Evolution pink XL 36

Vadi
January 1st, 2009, 10:27 PM
Doh, yes, that is a possible solution. The only worry about is that Applications need to be unique - at least I was hoping as such, because performance might be affected when querying by the string. But at least I have a possible base to now stand on. Thanks much!

(rather silly to miss out this idea of me)

pp.
January 1st, 2009, 10:32 PM
If you want to make sure that each application is written exactly the same each time it is mentioned, you'd add a second table containing the application names only. You'd then instruct you database to allow application names in the wide table only if they are present in the narrow one.

mike_g
January 1st, 2009, 10:34 PM
Doh, yes, that is a possible solution. The only worry about is that Applications need to be unique - at least I was hoping as such, because performance might be affected when querying by the string. But at least I have a possible base to now stand on. Thanks much!
I'd use an indexed application id for the search queries. You could then add the application name as an extra column, or add a separate linked 'application' table storing the name to avoid redundancy.