A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"feeder" table relationships



 
 
Thread Tools Display Modes
  #11  
Old March 15th, 2005, 06:04 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"Jamie Collins" wrote in
oups.com:

Essentially yes, although a CHECK constraint is more powerful than a
Validation rule e.g. a CEHCK can reference other columns in the same
row and columns in other tables.


A validation rule _is_ a check constraint: the Access UI coverts a VR
straight into a db rule. The only difference is that the UI still
appears uses a primitive version of ADO/ DAO and using SQL- DDL directly
does provide more functionality, but it's only a UI limitation.

Why is this a 'short cut'?


To me, usng a database schema to hardwire contraints that are purely
abitrary is bad practice, and is a short cut round creating a proper
table and suitable FK rules.

At one end of the extreme would be a validation rule (qua check
constraint like, "IN ('Red', 'Blue', 'White')", which would clearly be a
disaster when the first yellow car came along. Close down the database,
call in all the replications, run the DDL commands, recompile and
reindex the database, reconnect front ends, hope the outside code still
works, etc etc (okay, I am exaggerating, but you get the picture).
Compare that with just adding one record to a table.

At the other end of the extreme is an attribute like sex. There really
is little chance of "IN ('M', 'F')" ever having to be changed in our
lifetimes; but then again medical and surgical databases have to be able
to handle Unknown, Both, UsedToBeOneAndNowIsTheOther and so on.

In the end, db design is a skill as well as a technique -- the best
designs are the ones that make the best compromises^W trade- offs
between judicious short cuts and hopeless prolixity. But before doing
that, you do need to have explored both the long way round and the short
one in order to know what you lose or gain either way.

All the best



Tim F


  #12  
Old March 15th, 2005, 07:17 PM
rpw
external usenet poster
 
Posts: n/a
Default

Hi Jamie,

Thanks for taking the time to answer. I googled "SQL DDL access" and found
this site that explains where/how to enter SQL DDL:

http://www.databasejournal.com/featu...le.php/3460771

I wanted to see how this works so I made a Cars table with three entries
matching Tim's example. I pasted your 'code' into the ddl query and tried to
save it and got an error - "Syntax error in field definition" with the curson
then placed on the parenthesis in front of 'VARCHAR'. Removing the
parenthesis resolves that error.

The next error states "Syntax error in ALTER TABLE statement" then
highlights the second 'ALTER'. Removing the first line's semi-colon and
replacing it with a comma and removing the second 'ALTER TABLE' changes the
error to "Syntax error in field definition" and the word ADD highlighted.
Removing 'Cars', 'ADD', and 'CHECK' and typing in CONSTRAINT results in
another error "Syntax error in CONSTRAINT clause" and the parenthesis
highlighted. Removing the parenthesis resolves that error. The next error
is again "Syntax error in CONSTRAINT clause" but now the word 'IN' seems to
be the problem.

Anyway, I ran just the first (modified) clause and it worked but it seems
that no amount of 'tinkering around' and looking in Access' help files is
helping me get the syntax that works (I'm running A2K3 if that means
anything.) for the second clause. I understand that there are variations on
SQL language - is this problem I'm having getting the second clause to run
the result of one of those variations?

Thanks for your help.

"Jamie Collins" wrote:

rpw wrote:
does this 'code' go?


It's SQL DDL i.e. a Standard way of saying, 'Add this column and this
constraint to your table.' You could actually execute this DDL using:

CurrentProject.Connection .Execute "DDL here"

but obviously I'm guessing element names etc.

Isn't it adding a
field to a table and then adding validation rules?


Essentially yes, although a CHECK constraint is more powerful than a
Validation rule e.g. a CEHCK can reference other columns in the same
row and columns in other tables.

Why is this a 'short cut'?


Tim should address this one. AFAIK a row-level CHECK constraint
involves less overhead than a database-level FOREIGN KEY constraint,
however I think Tim's comment was meant pejoratively, so I'm stumped.

Jamie.

--


  #13  
Old March 15th, 2005, 07:35 PM
rpw
external usenet poster
 
Posts: n/a
Default

Thanks for the reply and explanation Tim. I guess I was thinking "short cut"
as saving time, but for my skill levels in Access, having the separate table
for colors works faster for me. Although I am trying to input Jamie's SQL
DDL into an Access query just so that I 'understand' how it works.

"Tim Ferguson" wrote:

"Jamie Collins" wrote in
oups.com:

Essentially yes, although a CHECK constraint is more powerful than a
Validation rule e.g. a CEHCK can reference other columns in the same
row and columns in other tables.


A validation rule _is_ a check constraint: the Access UI coverts a VR
straight into a db rule. The only difference is that the UI still
appears uses a primitive version of ADO/ DAO and using SQL- DDL directly
does provide more functionality, but it's only a UI limitation.

Why is this a 'short cut'?


To me, usng a database schema to hardwire contraints that are purely
abitrary is bad practice, and is a short cut round creating a proper
table and suitable FK rules.

At one end of the extreme would be a validation rule (qua check
constraint like, "IN ('Red', 'Blue', 'White')", which would clearly be a
disaster when the first yellow car came along. Close down the database,
call in all the replications, run the DDL commands, recompile and
reindex the database, reconnect front ends, hope the outside code still
works, etc etc (okay, I am exaggerating, but you get the picture).
Compare that with just adding one record to a table.

At the other end of the extreme is an attribute like sex. There really
is little chance of "IN ('M', 'F')" ever having to be changed in our
lifetimes; but then again medical and surgical databases have to be able
to handle Unknown, Both, UsedToBeOneAndNowIsTheOther and so on.

In the end, db design is a skill as well as a technique -- the best
designs are the ones that make the best compromises^W trade- offs
between judicious short cuts and hopeless prolixity. But before doing
that, you do need to have explored both the long way round and the short
one in order to know what you lose or gain either way.

All the best



Tim F



  #14  
Old March 16th, 2005, 10:57 AM
Jamie Collins
external usenet poster
 
Posts: n/a
Default


Tim Ferguson wrote:
A validation rule _is_ a check constraint: the Access UI coverts a VR
straight into a db rule.
it's only a UI limitation.


Sincere thanks for this. I had not picked up on this because the Jet
4.0 OLE DB provider exposes the CONSTRAINT_NAME property in the
CHECK_CONSTRAINTS rowset as '[column].ValidationRule' whereas the
corresponding property in the TABLE_CONSTRAINTS rowset is exposed as
'[table].[column].ValidationRule'. Silly me for thinking a
constraint's name would be consistently used g.

There really
is little chance of "IN ('M', 'F')" ever having to be changed in our
lifetimes; but then again medical and surgical databases have to be

able
to handle Unknown, insensitive suggested values snipped


If you had googled ISO sex codes you would have found 0=not known,
1=male, 2=female, 9=not specified. I wonder how many IN ('M','F')
Validation rules (or even IsMale YESNO columns g) have had to be
changed when overnight Jean Dupont = male suddenly becomes the Trustees
of the Estate of Jean Dupont Deceased = lawful entity?

Jamie.

--

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Replace table or table data in a database; keep relationships in t Mack General Discussion 1 February 24th, 2005 07:25 PM
Update - If statement Dan @BCBS Running & Setting Up Queries 13 December 14th, 2004 07:02 PM
Semicolon delimited text query help Al Guerra Running & Setting Up Queries 3 August 12th, 2004 11:50 AM
Complicated Databse w/many relationships Søren Database Design 7 July 13th, 2004 05:41 AM
Help with table design and relationships Richard Wright Database Design 3 June 20th, 2004 03:49 PM


All times are GMT +1. The time now is 05:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.