Implicit / explicit relationships in Access

[starson79]

Hi I've tried searching the Net for this but not come across any useful answer. I have just taken over several existing databases which I need to tidy-up / upgrade - whatever you can think of. After some investigation I realise that no table relationships have been defined whatsoever. However, all the "relationships" (implicit ones) are "defined" through the VBA code. Why is this? Why have no table relationships explicitly declared? Was it bad design practice? As far as I know - every database I've ever built or worked with has had table relationships declared! Referential integrity and all that!

I just wonder why it was designed like this in Access - the tables it uses are massive - because there is no normalisation (I guess if it's joined through queries then the relationships can be defined any old way - so no need to split data into separate tables). Lots of empty cells...
So is the advantage of not specifying table relationships down to speed? And it's easier to change relationships simply by changing the code, rather than deleting and re-specifying table relationships?
This is not a database that is used for a specialised function - it's just a database to hold masses of data for the usual stuff. Customers, orders, products...that type of thing.
Surely if the tables are massive in Access then (well the reason they have me here now) the database gets S-L-O-W! This negates the positive of a database with no table relationships defined.
Uh. Stuck.

Thanks for your help

 

[CBrighton]

I imagine it was lack of knowledge by the creator.

I'm self-taught and a lot of my early Access work was done with links being purely in queries or VBA recordsets (and the relationship window being empty).

 

[vbaInet]

Sounds like you've got a huge job ahead of you - normalizing, specifying PKs and FKs, getting the data into the right tables, changing queries; forms and reports to reflect the above change. A complete overhaul really. In cases like this I tend to build from scratch.

By the way, it may be that there are relationships but it wouldn't show unless the connecting tables are displayed in the Relationships window. Have you dropped all tables on there? Or is there really just one table? Also check out the Hidden tables too.

 

[starson79]

Hi,

Yeah from what I can see there are a few gigantic tables with many many tiny tables (one field to be precise!!). Yes I did think this would be a complete overhaul when I first looked at the database - I just didn't think the previous "database developer" would have left out normalisation and table relationships on purpose! That's why I queried it first!
I have checked the system tables - there were no entries in the relationships table.
So, if indeed it is true that the previous developer didn't know what he was doing (and it's not designed this way for any particular reason) then it will be an overhaul - ooh goodie exciting! Big job eh!

 

[vbaInet]

Big job eh!

Damn right it is!

But good luck with it though

My gut feeling is that the previous developer was initially asked to develop a small flat file database and as time went on it grew to a full fledged database. At which point it would take a considerable amount of time getting it normalized.

 

[gemma-the-husky]

and of course different developers have diifferent levels of experience and knowledge.

I am sure most of us look at things, and note that we possibly would have done them in a different way now.

and behind everything is the complex matter of the business logic that drives the database anyway. There must be a lot of legacy systems out there that would run better on modern technology - but would just cost to much to re-engineer.