Help Required with Article

[Uncle Gizmo]

I would be grateful for a bit of help folks. I am writing a Blog on the difficulties of extracting data from incorrectly constructed tables, or in other words data that doesn't conform to first normal second normal and third normal. If you happen upon, or have seen a recent post here in which the user has provided a sample database which nicely demonstrates this problem, then if you could point me to it I would be most grateful….

Sent from my SM-G925F using Tapatalk

 

[isladogs]

Happy to help but I'm just wondering whether it would be better to do so via private messages rather than identifying a thread called xxx by a user called zzz.
Just a thought

 

[The_Doc_Man]

Concur with Colin. Wouldn't want to embarrass someone by publicly stating that Joe Scheisskopf make a rookie mistake. If I see anything, will PM you.

 

[Lightwave]

I regularly come across plenty of enterprise grade applications with poor or sub optimal normalisation. Often understandably as they are legacy applications which have been continually developed over 20 years by multiple developers each with their own ideas.

One of the reasons why in my opinion and in a perfect world, big applications should be periodically redesigned from the ground up to take on board new things.

A significant number of government organisations are sitting with applications in databases that could be spatially enabled but because they were designed prior to spatial data types being available in the backends most applications have geography tacked on at the end. Now that most of the databases have these data types available they just sit being unused. Someone is going to come along and knock it out of the park.

New entrants are coming into the market with geography built in from the ground up and really threatening the resident systems often the main improvement is improved normalisation.

 

[Uncle Gizmo]

Thank you for your comments suggestions and links --- cheers Tony

 

[AnthonyGerrard]

Thank you for your comments suggestions and links --- cheers Tony

I wonder - (im expanding your remit!) if you could throw in a bit of dataware housing complexity into it?

 

[Uncle Gizmo]

I wonder - (im expanding your remit!) if you could throw in a bit of data warehousing complexity into it?

I presume you mean swap out the bulky text and things into lookup tables to reduce the amount of data?

 

[isladogs]

So its not just me who didn't really understand AG's request....
Still you had more idea than I did, Tony

 

[MarkK]

...legacy applications which have been continually developed over 20 years by multiple developers each with their own ideas.

Here's tweet you might like...

https://twitter.com/codewisdom/status/831588572310409216?lang=en

"We build our computer (systems) the way we build our cities: over time, without a plan, on top of ruins." - Ellen Ullman

 

[Uncle Gizmo]

....... I'm not sure that I have convinced him to go with a normalized schema but I tried.

I see that here all the time, someone comes in with a problem, due mainly to the construction of the database. I've noticed, often, three or four people will weigh in and reinforce the message.

There are two problems in convincing someone.. The first is it's difficult to see the benefits. The second is very often they've got a lot of time and effort invested in the position they find themselves in. I distinctly remember having done something similar myself when I was first learning!

It's quite easy to forget how long it took you to learn something. However recently I have had my eyes opened as I am trying to learn JavaScript. In essence JavaScript looks quite simple, it's basically the same as VBA you've got the same sort of structures.

However I've noticed there is something different about it, and I can't find any instructions on how to approach it. I think it's a bit like the concept of a collection in VBA. In Javascript (GAS) if you wanted to do something to a column of data in a Google spreadsheet you would access the whole set of Records and operate on them in one go, I can't for the life of me work out how the code works that does that!

I mention this because it's my struggling with JavaScript that makes me realise that MS Access isn't particularly easy to learn and I think it pays to bear that in mind when you answer people's questions.

 

[Lightwave]

Pat - I'm totally with you on the superb design of Access.

The report writer is simply outstanding - I m doing quite a bit in Crystal Reports at the moment. Its limited and the UI is just poor compared with Access. Yes you can do everything but I can't see myself ever being as quick as in access.

I've never really needed to touch SSRS as a result.

The combination of having pure SQL Editor / A rock Solid UI designer / A really quite powerful database AND a Full featured Code Editor wrapped up in a single program that deals with nearly all configuration and then having the opportunity to finely adjust configuration to include enterprise database backends as well is just outstanding. I would love to meet some of the original designers.

I think many went on to work on SQL Server

 

[Uncle Gizmo]

I was looking at your blog http://rounduptheusualsuspects.org/ the other day Mark, excellent stuff you've got on there. I've already posted one of your articles to my Facebook group... I note that Pbaldy Paul http://www.baldyweb.com/ has also got a good website. I wondered if we if I ought to start the separate thread where people can post a link to their blog? I should mention I do have one of my own I'd like to promote!

 

[isladogs]

I was looking at your blog http://rounduptheusualsuspects.org/ the other day Mark, excellent stuff you've got on there. I've already posted one of your articles to my Facebook group... I note that Pbaldy Paul http://www.baldyweb.com/ has also got a good website. I wondered if we if I ought to start the separate thread where people can post a link to their blog? I should mention I do have one of my own I'd like to promote!

Good idea. Go for it.
Saw you'd self censored the last sentence but its there in my notification email!!!

 

[Uncle Gizmo]

Good idea. Go for it.
Saw you'd self censored the last sentence but its there in my notification email!!!

I had second thoughts... It might be a spam magnet... Who's going to decide what should be allowed.... On the other hand this site is basically self-regulating so it should sort itself out.. Anyway I'm getting too deep into it!

 

[Lightwave]

I was looking at your blog http://rounduptheusualsuspects.org/ the other day Mark, excellent stuff you've got on there. I've already posted one of your articles to my Facebook group... I note that Pbaldy Paul http://www.baldyweb.com/ has also got a good website. I wondered if we if I ought to start the separate thread where people can post a link to their blog? I should mention I do have one of my own I'd like to promote!

Hi yes UG have visited your sites regularly - I am happy with my tag having a link to my blog I think overtime you will find that people will find sites if they are good mine is mostly visited by me but if you want to start a thread I don't think anyone would object.

 

[gemma-the-husky]

I see that here all the time, someone comes in with a problem, due mainly to the construction of the database. I've noticed, often, three or four people will weigh in and reinforce the message.

There are two problems in convincing someone.. The first is it's difficult to see the benefits. The second is very often they've got a lot of time and effort invested in the position they find themselves in. I distinctly remember having done something similar myself when I was first learning!

It's quite easy to forget how long it took you to learn something. However recently I have had my eyes opened as I am trying to learn JavaScript. In essence JavaScript looks quite simple, it's basically the same as VBA you've got the same sort of structures.

However I've noticed there is something different about it, and I can't find any instructions on how to approach it. I think it's a bit like the concept of a collection in VBA. In Javascript (GAS) if you wanted to do something to a column of data in a Google spreadsheet you would access the whole set of Records and operate on them in one go, I can't for the life of me work out how the code works that does that!

I mention this because it's my struggling with JavaScript that makes me realise that MS Access isn't particularly easy to learn and I think it pays to bear that in mind when you answer people's questions.

Uncle Giz.

The points you are making here remind me of an analogy I often use for Access. There isn't a learning curve. It's more of a learning staircase. You get to a cliff wall (the next step), and all you need is the "lightbulb" moment - someone shows you how/why to get to next level. Once you know that particular trick, you never forget it.

It reminds me of trying to follow an MS issued CD library when I was just learning. They had total album times, and I couldn't work out how they were summing the track times. It turned out they weren't - they just had an extra field for the album total.