"feeder" table relationships

[Guest]

I understand that I need to build table relationships based on IDs, not on
text values that may change over the life of the database. I have read in
this discussion group the perils of value lists in a table. Here's my
situation:

One foundation table named TBL_Project has eight fields in it that are FKs
(number), linked to small "reference" or "feeder" tables, and the only things
these individual tables have in them are PKs (autonumber) and their
descriptions (text). When we data enter, we need to see the descriptions on
the forms and on the reports. All eight fields will be data entered on one
form.

In addition, I presently have a one-to-many relationship but have played
around with not enforcing referential integrity in order to populate other
fields in TBL_Project. Prior to taking off the ref integrity, I checked to
see if the field was required or not.

So, my queries are: How do I see the values, not the IDs?
And
Is it okay to not enforce ref integrity in order to populate the rest of the
data in the foundation table? It would help if you took me through it slowly
if it involves any code, I'm not quite there yet.

Thanks.

Irena

 

[Guest]

In your project table, set up the FK fields as not required. In the
relationships, keep the referential integrity.

On a form, you setup a combo box. In the Format properties of the combo box
make the Column Count: 2
and the Column Widths: 0";1"
Under the Data tab, the Bound Column should be: 1

If you use Access' query designer view: When you build your query for the
report, include the 'feeder' table and just drag the text field from the
feeder table to the grid. Access will "know" how to keep the relationships
straight.

 

[Pat Hartman]

Using combos on the forms for the lookup values is standard practice. For a
report, where you use textboxes rather than combos, you need to base the
report on a query so that you can join the main table to the lookup tables.
Select the text fields rather than the id fields. Use left joins for all
the lookup tables if you have defined them as optional otherwise you will
miss rows from the main table if they have no value in the lookup field.

NEVER remove RI to "make it easy to input data". If you have trouble
inputting data with RI turned on, you have issues you need to deal with. RI
is necessary to ensure certain types of data validity. You can try to
control it yourself with code (many others have and been sorry they did) but
I don't recommend it. Always let the RDBMS enforce RI wherever you can.

 

[Tim Ferguson]

I understand that I need to build table relationships based on
IDs, not on text values that may change over the life of the
database. I have read in this discussion group the perils of
value lists in a table. Here's my situation:

I hate to make things more complicated for you, but I am not sure that I
buy into your original premise. You seem to have a design like this:

Colours
ColourID Description
-----------------------
1 Red
2 Blue
3 White


Cars
CarNumber Colour
------------------
1024 1
1045 3
1190 1

etc. Now, this makes sense if the colour chart is really unstable; ie, if
all this year's Red cars will be Flaming Sunset next year. If, on the
other hand, there is relatively little changing of colours of cars, then
this model has a lot of advantages:

Colours
Description
-----------
Red
White
Blue

Cars
CarNumber Colour
------------------
1024 Red
1045 White
1190 Red

This means that you don't have to make any joins to see what colours the
cars are. Note that you would use referential integrity by making
Cars.Colour a FK referencing Colours(Description), so that users cannot
enter a non existent colour. It's true that using a text field like this
is marginally less efficient than a numeric field, but what you lose in
update speed (I doubt it's noticeable) you will gain hand-over-fist in
query speed (definitely noticeable, especially if you have eight look up
tables).

Just a thought


Tim F

 

[Jamie Collins]

this model has a lot of advantages:

Colours
Description
-----------
Red
White
Blue

Cars
CarNumber Colour
------------------
1024 Red
1045 White
1190 Red

This means that you don't have to make any joins to see what colours the
cars are.

Just another thought: alarm bells ring in my head when I see a single
column table. That's why my notes say, if the colours rarely change
then just use:

ALTER TABLE Cars ADD car_colour (VARCHAR (20) NOT NULL);
ALTER TABLE Cars ADD CHECK (car_colour IN ('Red', 'White', 'Blue'));

Jamie.

--

 

[Tim Ferguson]

Just another thought: alarm bells ring in my head when I see a single
column table. That's why my notes say, if the colours rarely change
then just use:

ALTER TABLE Cars ADD CHECK (car_colour IN ('Red', 'White', 'Blue'));

Do you have any authoritative reference for these alarm bells? I don't
know of any reason within R theory to avoid one-column tables, or any
other kind of all-PK tables.

This design could work if you _knew_ that the colours would _never_,
_ever_ change. It is used sometimes, for example with Gender; but even
then it's something of a short cut.

All the best


Tim F

 

[Jamie Collins]

Do you have any authoritative reference for these alarm bells? I don't
know of any reason within R theory to avoid one-column tables, or any

other kind of all-PK tables.

I made it clear: just another thought and the alarm bells, both from
within *my* head <g>. But if you are looking for me to research
authorities for you <vbg>, I can recommend googling the exact phrase,
"When the attribute has a small set of values that do not change much
over time" and see who you get.

This design could work if you _knew_ that the colours would _never_,
_ever_ change.

You wouldn't want to do it too often, granted, but why 'never ever'?
Constraints can be dropped and redefined, data can be modified, life
goes on.

It is used sometimes, for example with Gender; but even
then it's something of a short cut.

Don't be afraid of the S word: google ISO sex codes (you may turn up
the same authority as before). What is the proper long way round of
doing it, then? Have a table of sex codes with a candidate key and
cascading FKs?

Jamie.

--

 

[Guest]

Sorry for these dumb questions: Where does this 'code' go? Isn't it adding a
field to a table and then adding validation rules? Why is this a 'short cut'?

 

[Jamie Collins]

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.

--

 

[Tim Ferguson]

I made it clear: just another thought and the alarm bells, both from
within *my* head <g>. But if you are looking for me to research
authorities for you <vbg>,

The standard texts (date, pascal, etc) are full of single-column tables
so I'm not looking for anything.

You wouldn't want to do it too often, granted, but why 'never ever'?
Constraints can be dropped and redefined, data can be modified, life
goes on.

Just because they can be changed, it doesn't make it a good idea.
Building obsolescence into a data model is not clever at all.

Have a table of sex codes with a candidate key and
cascading FKs?

No question of candidate keys or cascading anything: see the example I
quoted above.

All the best


Tim F

 

[Tim Ferguson]

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.

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

 

[Guest]

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/features/msaccess/article.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.

 

[Guest]

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.

 

[Jamie Collins]

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

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.

--