When to make relationships between tables

[mainerain]

What would be the rule of thumb to determine when to make relationships between tables? Subjective at times I guess. Say you have a main table which contains multiple fields that use Lookup tables, when would you want to create relationships to these Lookup tables, if ever?



Thanks

[Bob Fitz]

I think most people here would advise against using lookups in fields. See: http://access.mvps.org/access/lookupfields.htm

[mainerain]

I think I miss spoke. I was referring to a main table that has multiple fields which get filled with a form that has a combo box which gets it's data from a separate table. Is there value to then create a relationship between the main table and the separate table that was used in the forms combo box?

[CJ_London]

you don't have to create relationships, but if you do it gives you more control over the data. Up to you whether you want or need that control

Say you delete one of the records in your lookup table. Now your main table cannot find it so if you still want to see the main table record joined to the lookup table, you will need to use a left join. left joins are slower than inner joins and potentially more work to handle the exception (handling nulls for example). Apply a relationship and enforce referential integrity and you cannot delete the record in the lookup table until none of the main table records reference it.

Just having a relationship without enforcing integrity doesn't achieve much except it helps you and others to understand what relates to what and of course provides the join when developing queries.

[mainerain]

Thanks for the info, appreciated

[isladogs]

Following on from @Ajax's comments, please see my three part article on relationships and referential integrity
https://www.isladogs.co.uk/relationships1/index.html

[mainerain]

Thanks isladogs, enlightening.

[isladogs]

You're welcome. I wrote the article as I had never found a clear reference as a link for use in forum posts.
The final page on the MSysRelationships table was additional info for anyone interested. That isn't required information to use relationships effectively!