Jump to content

Databases


peeriebryan
 Share

Recommended Posts

Here's one for the geeks. Probably the first of many threads about such stuff as I'm doing an IT course

 

Am I right in thinking that Microsoft Access is a load of sharn. I'm being forced to work with it as its part of my course requirements, which goes against the grain as its PC only and I'm a Mac-er

 

I have a peerie bit of experience with MySQL/PHP, which seems a lot more flexible and scaleable when compared to Access

 

Discuss! :wink:

Link to comment
Share on other sites

Not a complete load of sharn, no. I developed using versions of Access for many years and found it quite ok in lots of regards.

 

Access has a GUI front-end which makes developing your database more visual and can be a lot quicker. It's pretty powerful in terms of features and capabilities (even supporting some which higher-end database servers (SQL Server for example) don't: cross-tabs, cascade-delete relationships and others. Having a complete programming language (albeit Visual Basic) behind it, you can do pretty much everything you need very easily from within a single application. All well and good so long as you don't want a web interface!

 

On the downside, it's not designed for large-scale or distributed applications; it's really best suited for small, local applications. Anything requiring more than a couple of simultanous users or large volumes of data and Access really runs into trouble. It certainly doesn't support the level of security you'd expect to find in a multi-user distributed environment.

 

MySQL is quite ok from my experience, but I haven't used it extensively for quite some time. I did find it more time-consuming and cumbersome to develop with overall but much of that was to do with the environment (OpenBSD running through Telnet, connecting back via my browser to view the end-result, among other hoops I needed to jump through) than it was a problem with MySQL. From what I could tell MySQL is just another database server, doing database server-y things. Once your database is designed and implemented, having a proper way to talk to it is vital and I'm not convinced that a PHP client is a hugely good idea if you want to keep your sanity.

 

I'm developing in SQL Server these days and find the added 'power' comes at a cost of rapid development. The lack of decent relational features (maybe this is fixed in SQL Server 2005, not sure) has caused headaches and are something I miss.

 

Access certainly handled it's relationships properly.

Link to comment
Share on other sites

Many to many joins

 

There's your problem right there! Avoid many-to-many joins. I'm pretty sure they're never necessary and, if they are, they're much better implemented with (as you say) three tables anyway! ;)

 

I would be interested to know the proper way to get around an example that I used to use, many computers with many applications on them. Is there a more logical way than a 3 table many to many relationship?

Link to comment
Share on other sites

Microsoft Access is quite a good environment to get people up to speed in database mechanics as a starter for ten.

 

You have an easy to use intuitive interface, able to create front ends within the application, you have VBA to enhance your application. Then you also can create VB modules to pull in windows API from within Access, you are able to use COM components to talk to other Office applications and other applications altogether etc. etc. Quite a lot really :)

 

As has been pointed out there are better solutions for distributed and large scale applications. Yes, moving into server based architecture here is your best bet. I have however, worked in a situation where an Access database had been hacked to the point it was just a shell for modules and held over 2Gb of data. Yes .. you read that right!! This was also a mission critical application! (I didn't build it .. had the sorry job of maintaining and migrating it!) Access is NOT meant to be able to run that...! I'm just mentioning this as a lot of people do slag it off for being a toy database - which is an unfair title. It is what it is.

 

On the downside, it's not designed for large-scale or distributed applications; it's really best suited for small, local applications. Anything requiring more than a couple of simultanous users or large volumes of data and Access really runs into trouble. It certainly doesn't support the level of security you'd expect to find in a multi-user distributed environment.

 

This is true .. though as with what I was waffling about it can be moulded into something altogether different.

 

As fjool says "Not a complete load of sharn, no. I developed using versions of Access for many years and found it quite ok in lots of regards. " .. this is my take on it too.

 

Your course though will ideally have sections on normalisation? This is where the real meat of a database lies in my opinion. If it's not planned out from the start .. it doesn't matter how fancy the front end is ...

Link to comment
Share on other sites

Oh. Forgot to say .. you'll find that all of the major banks and nearly all of the large blue-chip companies and others are all trapped in a Microsoft centric environment and use Access as their database of choice for the majority of departmental database applications.

 

Once out in the job world looking for a position you'll likely be expected to be able to use Access - i.e. build from scratch and or maintain / migrate systems that are already in place. An "Access Developer" role per say.

 

Large scale PHP/MySQL or even .NET / SQL Server jobs are mostly defined as "Programmer" jobs - where you are expected to know the ins and outs of the "programming" language so as to be able to create front ends.

 

The other side to this is Sybase and or Oracle et al - which is another angle and world altogether....

 

.... Oh for the joys of IT?!?

Link to comment
Share on other sites

Perhaps I'm talking slightly at cross-purposes. I was responding specifically to your comment that

Many to many joins require the use of three tables for instance.

as if this were a failing of Access.

 

I agree that three tables are necessary for this scenario: tComputers, tApplications, then a table which lists which applications are on which computer. All I mean is that I'd never want to create a many-to-many relationship directly between tComputers tApplications, since this would be horribly denormalised. The third table is necessary for satisfying the ever-elusive 3rd-normal form, not an inconvenience.

 

Since each computer has many entires in the middle table, and each application has many entries in the middle table, you have no direct many-to-many relationships at all but achieve the same result via an intermediary pair of one-many joins.

 

I guess my point is only a question of terminology; a many-many relationship implemented as a pair of one-many relationships is much easier to design around than trying to implement with only two tables. I suppose the end result is still theoretically a many-many relationship, it's just implemented indirectly.

 

Access is correct (in my opinion) in the way it insists that you do this.

Link to comment
Share on other sites

  • 1 month later...

Can any of you databasers help me out with a Microsoft Access problem which has been doing my nut in! I'm trying to establish a couple of relationships between tables but I'm rapidly getting nowhere

 

I want to set up a recursive relationship within a table as follows

 

Employees (in a table called 'employees') can either be junior or senior. Senior employees can supervise junior employees (a junior employee can be supervised by one or more seniors, but not all seniors are supervisors)

 

Additionally, a branch (in a table called 'branch') employs many employees, but one employee is the manager of that branch. As far as I can see, that's two relationships between the tables, which access won't let me do

 

 

Does that make any sense to anybody!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...