473,320 Members | 2,004 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

LookupTable with Referential Integrity and Cascaded Updates

I have a 15 LookUp Tables tied to 15 ComboBoxes.
Some of the Tables contain 3 to 10 Recs, and a couple contain up to 50
Recs.

1- Is it a CommonPractice to link the field that gets the ComboBox
result, to the Lookup Table to ensure Referential Integrity and
Cascaded Updates?

2- Also, should the LookupTable PrimaryKey be an AutoKey or the Name
of the main field being referenced, which in my case would average 15
chars, but in two cases be 50-chars long? The AutoKey would be
smaller, but given the size of the Tables may not be necessary?

3- Should I store AutoKey value, or the Value of the main field being
referenced?

Thanks
Greg


eld or is the MainField sufficient as the Primary since each will be
unique, BUT may be up to 50 characters long in a few instances??

2- Should each LookUp Table be linked back to the maintable at the
ComboBox

Feb 27 '07 #1
4 2051
On Feb 26, 8:22 pm, "ApexD...@gmail.com" <ApexD...@gmail.comwrote:
I have a 15 LookUp Tables tied to 15 ComboBoxes.
Some of the Tables contain 3 to 10 Recs, and a couple contain up to 50
Recs.
1- Is it a CommonPractice to link the field that gets the ComboBox
result, to the Lookup Table to ensure Referential Integrity and
Cascaded Updates?
Keep in mind that controls are primarily a means of entering and
viewing data. Don't think of them as being a part of the data or table
structure. If you are using (or intend to) Relationships and Integrety
then yes, the target table and the "lookup" should be connected on the
relevant field.
2- Also, should the LookupTable PrimaryKey be an AutoKey or the Name
of the main field being referenced, which in my case would average 15
chars, but in two cases be 50-chars long? The AutoKey would be
smaller, but given the size of the Tables may not be necessary?
The type of index you use is entirely up to you and the needs of
the application. Sometimes it is beneficial not to use AutoNumbers,
sometimes it is. I usually prefer them as the information they
represent can be changed without needing to affect other data.
Sometimes this is not a good thing. You decide what's best for your
app.

3- Should I store AutoKey value, or the Value of the main field being
referenced?
Whatever field the relationship is based on (usually an index
field) is what you would store.

Feb 27 '07 #2
Thanks Mike

This will probably add an additional 15 Indexes that Access will have
to manage over a network just to ensure Cascading Changes to each
single field. It's nice to have the feature, but will this negatively
impact performance much?

I think I'd like to use the referenced field as the PrimaryKey instead
of an AutoKey, but am I asking to much from Access to deal with a
PrimaryKey field of 50-Chars long
over the network?

ThanksAgain
Greg

Feb 27 '07 #3
On Feb 26, 7:57 pm, "ApexD...@gmail.com" <ApexD...@gmail.comwrote:
Thanks Mike

This will probably add an additional 15 Indexes that Access will have
to manage over a network just to ensure Cascading Changes to each
single field. It's nice to have the feature, but will this negatively
impact performance much?
Not if as stated, the most you'd see in the table is 50 records.
Access will only transfer the indexes that are in use or requested,
not all of them (unless requested).
One other thing I've done in the past to reduce the number of "lookup"
tables is to combine a bunch into one. Mind you this is not really a
good method for selecting values that are a part of a large record,
but maybe for selecting Provinces, States, Currency Types, Format
options ect. As an example you might do this...

Table: tblCommon_Selections
SelID (PK) 'Index for selection
SelText 'Text or description of selection item
Catagory 'A way to group them for different combos

Example Data

ON / Ontario / Provs
PQ / Quebec / Provs
OH / Ohio / States
NY / New York / States
SD / Short Date / Formats
LD / Long Date / Formats

Different combos could filter for thier rowsource by including a
WHERE [Catagory] = blah blah
I think I'd like to use the referenced field as the PrimaryKey instead
of an AutoKey, but am I asking to much from Access to deal with a
PrimaryKey field of 50-Chars long
over the network?
Not really. Fifty chars is really not a lot of data - a 14.4K modem
would transfer that in less than a second. But I feel that an index
should be used as a short identifier, not actual data, so in my
opinion it kind of defeats the purpose. But there's not a rule that
says you can't do it that way.

Feb 27 '07 #4
Once again, Thanks for all the valuable insight.
I will forge ahead with seperate Lookup Tables and ponder the index
issue.
I believe the performance will be fine. If not, I have other options
now.

Thanks
Greg

Feb 27 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Greg Allen | last post by:
I am trying to use XML to update customer information in our SQL server database. The customer data has some referential integrity. A simple example would be two tables, employee and department...
2
by: Bob Rogers | last post by:
I seem to have gotten myself into a bit of a pickle. I am trying to delete a record from a table attached to a form, and it complains about not being able to delete a record in a table because...
3
by: Dave | last post by:
I have always taken it for granted that once RI is in place, no orphan records can be created, and that RI can't be put in place while orphans exist, but today I came across a situation where that...
1
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into...
7
by: Jimmie H. Apsey | last post by:
Referential Integrity on one of our production tables seems to have been lost. I am running Postgres 7.1.3 embedded within Red Hat kernel-2.4.9-e.49. Within that I have a table with referential...
1
by: HGT | last post by:
Hello all, I am currently on a project which the source data come into the databases is always dirty (not surprisingly); however, due to the design of the database, it is very difficult to...
3
by: Wayne | last post by:
I've inadvertently placed this post in another similar newgroup, and I apologise if you get it twice. I'm building a database that consists of frontend and backend. Some of the lookup tables...
2
by: ApexData | last post by:
Access2000, using a continuous form. I’m getting a message that say “you cannot add or change a record because a related record is required in table Employee”. This occurs in all my combobox...
1
by: Greg (codepug | last post by:
When it comes to LookupTables for dropdown ComboBoxes does it make sense to create an autonumber as the primary key along with main value say IllnessType and then have the combobox store the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.