Relationship Problem

[The_Doc_Man]

I don't envy you the job. I had to retrofit a badly designed database while it was still in use. Ho, man, what a mess it was. The guy who designed it was not so hot and made about a dozen limiting decisions. Took me a year to get it where it was even CLOSE to reality. But the boss didn't want to take the time to do a rewrite, either. In retrospect, I should have told him that it was going to take longer to clean up the mess that was made AFTER I had it half fixed than it would take to just run with the old one while I rebuilt it all anew in parallel and then just transferred the data. But at the time, politics was knocking at the door. Not mine. His. Should have known better, but like Jim Mora, former NO Saints head coach once said, "Woulda, Coulda, Shoulda" don't count for a hill of beans if you didn't, didn't, didn't.

 

[suchiate]

Thanks for sharing =)

Currently, the old design is still running, but we are doing a parallel migration, i slowly redesign certain portions of design and codes and then they test, once tested, then migrate the certain portion to the new design and upload the new modules. Not sure if it's a good solution but, just following orders

Just wondering if Access has similar things to SQL Server DTS? because I have to always write engines to the migration..

 

[The_Doc_Man]

Unfortunately, Access tends to not automate a lot for you at the level of data transfer services. You can do import or export to various formats. You might be able to do what you want in a couple of steps, writing queries to build the exported data in its required form (and export that to a file), then in your newer layout, import the files via the Import wizard's ability to either append data to an existing table or to a new table. If you did the export right, your tables will already be correct for direct import.

 

[suchiate]

Thank you very much,

Doc_man, do you only use Access or you are also using SQL Server 2005 and other databases? I am thinking of migrating the system to SQL Server in future, so thinking to ask whether people still stick to Access alot or they are all slowly moving towards SQL Server and so on?

 

[The_Doc_Man]

In my shop, I have Access and ORACLE, but they don't talk to each other. So I generally avoid questions that go in the direction of ODBC details. Not totally avoid, if I know the answer. Never used SQL server, though I'm told another project on our grounds has such a thing. Except for direct SQL questions, that's another topic I leave for the more directly initiated.

My strong points are devious, efficient code and convoluted but highly normalized tables. Plus I'm an old bit-twiddler from the days when assembly language was the ONLY way to fly. Not to mention more than a passing interest in DB theory and design. Originally, I'm trained as an analyst - in the field of chemistry. But analyzing problems in ANY venue requires the same thing: Organized thought and the ability to break apart processes to see what's inside. Plus the ability to expand back from micro to macro levels when necessary.

Having placed the disclaimers, there is nothing wrong with the idea of going to a mixed DB, with an Access FE and an SQL Server BE. Your network folks would love you for it. Your users would be amazed at the speed difference, since you no longer have to load 100,000 records to get a few lousy lines on a report.

Big hint, though. If EVER you decide to do this, spend money for a nut-crusher of a server for your SQL box. You can make workstations out of lousy, underpowered wimpy boxes if all they have to do is pass queries to a beast of a central server and format a few pages here and there. If you have anything to say about it, overconfigure your SQL box. You can afford to take it easy on other boxes. Buy extry memory and extra disk space, maybe in the form of a small storage array with drive mirroring for data security. RAID-5 if not RAID-1.

 

[suchiate]

Wow! I'm impressed and motivated by your years of experience, I still have lots to learn in this field, thank you for sharing your knowledge, I'd love to learn as much as I can when I can

Are we beginning to go out of topic?

 

[The_Doc_Man]

There was a topic

Oh, yeah... I guess so.

Back to the subject at hand. Normalization is important because it means your DB won't bloat on you as fast as it otherwise might. It keeps you from losing information about a parent when the last child goes away.

In the case you described with multiple sparse tables, one for each plan, my instinct (known to be overly cautious at times) says to merge tables as much as possible.

 

[suchiate]

Alright, thanks for all your suggestions and advice, would you mind if I PM you in future for more discussions?

 

[The_Doc_Man]

My profile is set for no PM and my firewall will block IM operations. One of the duties I had pre-Katrina was teaching Windows Security to new Systems Administrators - since the colleges really don't teach THAT much about it. They have so many other topics to hit that they just can't afford to make a security course a requirement.

The upshot of all that is I never allow much into my box at home, and my box at work is even more tightly secured because of the large number of firewalls and proxy servers to be navigated. But then, at work I'm on MilNet, which is about as paranoid a network as was ever designed.

 

[suchiate]

oh.. alright then, let's depend on fate to see if my topic title would interest u again in future

 

[dfenton]

Nah, Access wont allow relationships with different data types, AutoNumber must be related to AutoNumber.

Autonumber links to long integer.

Autonumber is not a different data type -- it's just a long integer field with a special kind of default value.

 

[suchiate]

Autonumber links to long integer.

Autonumber is not a different data type -- it's just a long integer field with a special kind of default value.

Hi dfenton,

Thanks for your concern, please do go through all the posts as that issue has already been put to an end.