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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"feeder" table relationships
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 a 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 |
#2
|
|||
|
|||
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. "IrenaY" wrote: 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 a 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 |
#3
|
|||
|
|||
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. "IrenaY" wrote in message ... 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 a 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 |
#4
|
|||
|
|||
=?Utf-8?B?SXJlbmFZ?= wrote in
: 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 |
#5
|
|||
|
|||
Tim Ferguson wrote: 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. -- |
#6
|
|||
|
|||
"Jamie Collins" wrote in
oups.com: 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 |
#7
|
|||
|
|||
Tim Ferguson wrote: "Jamie Collins" wrote in oups.com: Just another thought: alarm bells ring in my head when I see a single column table. 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. -- |
#8
|
|||
|
|||
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" wrote: Tim Ferguson wrote: 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. -- |
#9
|
|||
|
|||
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. -- |
#10
|
|||
|
|||
"Jamie Collins" wrote in
ups.com: 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 |
|
Thread Tools | |
Display Modes | |
|
|
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 |