[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I'm designing a completely new schema for my database. A major
criterion is that it facilitate ad-hoc queries via MS-access, excel and
OpenOffice, presumably with ODBC.
My question regards the use of UNIQUE constraints instead of PRIMARY
KEY's on some tables. Both result in an index on the specified tuple
of fields, so I presume query performance shouldn't be much different.
Using UNIQUE constraints seems to let me better match the natural
structure of my data. A 'run' contains a sequence of 'opsets'.
Each opset contains a sequence of (a few) 'step's.
run-foo
opset-1
step-1
step-2
opset-2
step-1
So the 'steps' table is logically indexed by (run, opset_num, step_num).
But some opsets are not in runs, and some steps are not in opsets, so
I would have step.run be null in some cases, likewise step.opset_num.
Null values mean I can't use these fields in a primary key, so I
propose to use UNIQUE constraints instead.
What am I losing by not using PRIMARY KEYS? Will ODBC clients have
difficulty dealing nicely with the database? Will the planner produce
lousy query plans? Will Edgar Codd haunt my dreams?
-- George Young
I think I can give you some insights about MS Access to help you. In MS Access, you can specify a column as a "primary key"; which basically means the column is indexed and must contain unique values (also, nulls are not allowed). I have run into problems depending on columns being "primary key" in MS Access in db apps that receive data in batch file uploads from other sources (e.g., uploading 1,000+ records into a table). Is sounds like your requirement to use MS Access for ad-hoc queries means that you will have some users that want to access the database with MS Access as a "front-end" client tool. If that is the situation, then you don't need to worry about the structure of the table as MS Access relies on ODBC for this. You may also want to communicate to the end users that MS Access is not a client-server tool; in other words, all of the records are transferred from the server to the client's box and then the query is executed.
I hope that helps.
