Re: some basic db questions
- From: Nis Jørgensen <nis@xxxxxxxxxxxxx>
- Date: Wed, 23 May 2007 02:39:01 +0200
cfriedalek@xxxxxxxxx skrev:
Hi. I'm new to databases and this group. Hope this is the right place
for me to post my questions. If not please excuse me and let me know
where I should post as an alternative.
I have computed temperature data which depends on five parameters.
Each parameter has two or more unique values. The collection of five
parameter values uniquely identifies a temperature value. Temperatures
themselves are not unique. Since I have thousands of temperature
values and from two to one hundred of the parameter values I
understand that I should create six tables in all ie. temperature,
param 1, param 2 , param 3, param 4, param 5.
Since within any param table the values are unique I set them as
primary keys, rather than include an integer id column as seems to be
common. Adding an id seems to be of no value but I don't understand
why?
In the temperature table I create a temp column and five columns that
mimic the parameter fields. I also add a foreign key constraint to tie
the temp table param colums to the parameter tables. OK so far (I
think).
I generated the a db along these lines using python, sqlalchemy,
sqlite3. Then I generated the following SQL transaction script using
SQLite Database Browser tool to show what I got.
BEGIN TRANSACTION;
CREATE TABLE axial_locations (
axloc NUMERIC(10, 2) NOT NULL,
PRIMARY KEY (axloc),
UNIQUE (axloc)
);
CREATE TABLE boundary_conditions (
bc VARCHAR(3) NOT NULL,
PRIMARY KEY (bc),
UNIQUE (bc)
);
CREATE TABLE inverse_powerlaw_indexes (
s INTEGER NOT NULL,
PRIMARY KEY (s),
UNIQUE (s)
);
CREATE TABLE shapes (
shape VARCHAR(10) NOT NULL,
PRIMARY KEY (shape),
UNIQUE (shape)
);
CREATE TABLE transverse_locations (
trloc NUMERIC(10, 2) NOT NULL,
PRIMARY KEY (trloc),
UNIQUE (trloc)
);
CREATE TABLE temperatures (
temp NUMERIC(10, 2),
shape VARCHAR(10) NOT NULL,
bc VARCHAR(3) NOT NULL,
s INTEGER NOT NULL,
axloc NUMERIC(10, 2) NOT NULL,
trloc NUMERIC(10, 2) NOT NULL,
FOREIGN KEY(shape, bc, s, axloc, trloc) REFERENCES shapes (shape,
bc, s, axloc, trloc)
);
COMMIT;
This bit doesn't look right:
FOREIGN KEY(shape, bc, s, axloc, trloc) REFERENCES shapes (shape,bc, s,
axloc, trloc)
I am not familiar with SQLite, but in Standard SQK, this should be
something like
FOREIGN KEY (shape) REFERENCES shapes (shape),
FOREIGN KEY (bc) REFERENCES border_conditions (bc),
etc
You should also have a primary key defined for your table. According to
your description, this would be
PRIMARY KEY(shape, bc, s, axloc, trloc)
1. Since this is my first attempt at creating a database. I hope
someone can tell me if I have made any serious mistake in my table
design. For example could I have (or do I need) a single column in the
temperature table that somehow references all five of the parameter
tables columns, rather than a separate column for each foreign key?
It looks reasonable so far, except for the issues above. One thing to
note: You could also store all data in the temperature table, with
almost the same result. What your model gives in addition is a fixed
list of the possible values for the field - a list which can be easily
modified, but which cannot be accidentally violated. If one of your
"parameters" can really take any value within the domain, then you could
simplify the model by removing the table. Similarly, if you are
absolutely sure that "shape" will only ever be "CIRCLE" and "TRIANGLE",
you can just add this constraint to the temperature table and get rid of
the shapes table
2. I can populate the parameter tables easily enough. However I am
unclear how I populate the temperature table. I put a temperature
result in the temp column ok, but what do I put in the parameter
columns within the temperature table? Do I enter the parameter value?
Doesn't this fill up the database with redundant data? Or perhaps this
is just a reference within the db engine to an entry in the respective
parameter table?
Look at the parameter tables as just lists of the allowed values. You
still need to store the actual values associated with each temperature
measurement - by storing the a key to the parameter table. With your
data model, this means storing the actual data in the temperatures table.
How the database stores the values internally is really unimportant when
you are doing the data model design. The important thing is that the
same information is not stored in multiple places - not that the same
data values are not repeated for different rows, relating to different
pieces of imformation.
3. Does the specification of uniqueness of the parameter values in
their tables prevent me from mistakenly entering an incorrect value in
the corresponding temperature table? (Actually it doesn't as I
discovered by entering a SQL command in the SQLite Database Browser
tool). Somehow this seems to miss the point for me. Can I, and if so
how, tell the temperature table that any of the parameter entries can
only come from values in the respective parameter tables?
This is what the FOREIGN KEY declarations I write above should be doing.
/Nis
.
- Prev by Date: Re: PostgreSQL or MySQL ?
- Next by Date: Re: PostgreSQL or MySQL ?
- Previous by thread: Re: PostgreSQL or MySQL ?
- Next by thread: Midget amongst Eagles
- Index(es):
Relevant Pages
|
