Lookup Tables

[BikeToWork]

I am working on a a denormalized database that has about 5 main data tables, and about 70 lookup tables. It is currently set up with lookup fields and multi-valued fields which I am replacing with normalized fields. The goal is to make the database upsizable to a SQL Server back end. What necessity is there for making relationships between the main and lookup tables? The lookup tables are used only for combo dropdowns on forms. If there were relationships (no referential integrity and no cascade updates and deletes) between the 5 main tables, which all share the same lookup tables, and these lookup tables, it would be the ugliest mess of spaghetti around. What, if anything, do such "non-binding" relationships do for an Access database? Is there any need for them. Thanks in advance for any advice.

 

[lameid]

These relationships show up in the relationship viewer giving you an Enity Relation Ship diagram... Also I believe this it what causes the query designer to autocomplete the joins between your tables but that may only be true for older versions of Access (I always define my relationships, so I do not know if it changed).

Noteworthy is that the Access Upsizing Wizard to SQL Server will convert relationships in most cases with varying degrees of consistency (either actual relationships or triggers).

Some developers argue to create the relationships in SQL manually rather than upsizing them (upsizing relationships is an option on by default) as the naming convention created is not obvious. That being said if you are migrating the data to SQL anyway, it may be best to wait and do it on the server side rather than taking the time to do it in Access. I would however do any restructuring in Access as it should prove faster.