question about SQL Server for business
-
I am doing a paper that is about getting a systems development plan for five fitness centers. I need to know if this database would work for something like that. I am new to all of this and have no idea about databases so any help would be very appreciated. Thanks
-
Yes SQL Server will work for the project, so would Oracle, Sybase, and DB2. Depending on the requirements you may also use MySQL, or PostgreSQL but would run into some additional work if you wanted advanced replication and clustering for failover and fault tolerance issues.
About the only thing that will not work is Access, or any database on a Mac.
Hope this helps-
Dave
-
Don't dismiss Access so quickly without knowing the details of the required application. Depending on the needs of the company and what they want to do, Access could well be a possible solution. In 1998 I developed an Access application that was truly an Enterprise level program. The limitations of Access were overcome by a distributed replication system allowing multiple back-ends to even the load. Not one database crash in 6 years of operation. The back-end was ultimately replaced in 2005 with SQL Server 2000 due to the size of the database (1.5GB).
-
Deciding which database technology to use for your system can be a complex task and, as such, there are no right or wrong answers to your question. Much depends on the requirements you have to satisfy. For example, budget number of users; centralised database; application architecture; UI requirements etc..
In my view a SQL Server engine is generally the best (and, typically) only way forward but Access does have its place if used appropriately.
You can always start with SQL Server Express Edition. This provides a cut down (but fully compatible) version of SQL Server that can be distributed free of charge to your clients (I think?). You can then migrate to SQL Server 2005 (Standard Edition) at a later date.
-
All of this information this very helpful. The one thing it does say is the system will be used by both members and fitness consultants to track participation in various fitness activities, such as free weights, volleyball, swimming ect.
Then it says use a database management system to define the various tables that will make up the database.
So is this still something you all recommend I use? Thanks for answering my questions.
-
Seriously, don't dismiss Access as a possible piece to the puzzle. Access has a thing called Access Projects which acts as a front end to a SQL Server back end. What this means to the SQL Server developer is that the application portion can be developed using Access to access the data stored on SQL Server. Also using the Access Upsizing Wizard, you can easily migrate the Access code to SQL Server. Using Access Projects, gives the developer easy access to SQL Server development. It allows the user to modify tables, views, triggers and stored procedures using a GUI based tool. It also has a decent reporting tool that can be used to generate reports. So Access shouldn't be dismissed so off handedly. We use it all the time on SQL server projects here where I work.
-
My science research start-up company began their serious data gathering in Access because it came at no additional cost with Office Pro 2003, and we were small with few users. We are now converting this fully normalized Access 2003 mission critical database into a SQL 2005 backend with an Access 2003 front-end. Here are some of my learnings so far (still converting):
The Microsoft converting wizard did a fair job, but only for the tables.
- Did not bring over the caption (field description) fields
- Did not recreate all the Foreign Keys, and thus lost some of the relationships
- Does not convert queries, forms, and reports
For the queries, forms, and reports we used a product from Austrailia, ConvertU2, http://www.convertu2.com which created all the Access queries, forms, and reports AND fixed 96.5% of the tedious conversion items (about 11,000 code changes).
Gotcha's we discovered so far:
- All the lookup combo boxes for easy to create and use pick lists in the tables and queries are not available in the SQL tables or Views. Must use forms to recreate this feature of Access. (We are going to have to create a lot of maintenance forms for where before our users just went into the tables or queries to fix data.)
- Cannot use Access 2003 adp to modify the SQL back-end, must use Access 2007 (Office 2007 isn't out here yet). Must use SQL Server Mgmt Studio to maintain the SQL backend database. Not sure we want end users using this powerful tool.
- We are going to have to find another mechanism (not Access) for our users to create their own queries (views).
- Queries with lots of computational expressions are very messy to recreate in SQL views. Now exploring hopefully easier to maintain alternatives to creating these needed views.
Will let you know more when we get into production.
-
The limitations of Access can also be over come by using linked tables and placing each table in it's own database. However, this tends to get messy managing a different db for each table.
-
You'll need to look into data modeling - you need to make tables to define something like:
CLIENT has 0 or more CONSULTANTS
CONSULTANT has 0 or more CLIENTS
CLIENT has 0 or more SESSIONS
SESSION has 0 or more ACTIVITIES
The all caps are Entities - commonly known as tables. You define relationships between them to enforce data integrity. All the examples are 0 to many, but you can have 1 to 1 and 0 to 1. A many to many is actually made up of 3 tables (In my example CLIENT SESSIONS and ACTIVITIES). A client may have many activities and an activity may have many clients.
You may need to capture billing info and other data as well, based on the depth of your assignment.
Search google. You seem to have a pretty tough assignment thrown at you for not knowing much about databases! Do You actually have to do the modeling in Access or just show a model (ERD)?
And I also agree that access and sql server paired up would be a viable system for something like this. And access would be a good choice for a beginner to get into.
-
Sam,
To be truthfully honest with you I have no idea. I tried to get ahold of my instrutor to see what he really wanted. I think it just has to be explained either in essay style or some other kind of format. I am just trying to get pieces of this done and then put them all together. My problem is I am running out of time because it is due on Sunday. I know that I don't have to show any of my work actually using Access though. I really liked how you explained things. Everyone here is being very helpful and I very much appreciate it. Teresa
-
You're very welcome!
I'm sure if you give him an ERD created in Visio He will be impressed. Wikipedia is always good
As far as the access solution goes - all the fitness centers could use the same database backend if they were in a close geographical location. If they were located far apart, they would have to use several servers 'sync'd up' with replication (which someone has already mentioned above).
-
'About the only thing that will not work is Access, or any database on a Mac.'
I doubted that statement - and it made me curious about filmaker pro's capabilities.
http://www.rhinorecords.com/ is running filemaker pro. If the app was hosted on a webserver/db server they would probably be good to go.
"Rhino's Web site now uses FileMaker Pro for all its database-related functionality, from the Web search engine to contests and online surveys. Three related files, the largest of which is almost 15MB, serve up more than 30,000 CD tracks located on some 2,500 CDs. The site receives between 150,000 to 250,000 hits per day and 100 to 150 database requests per minute. The FileMaker Pro system performs significantly faster than the old SQL-based solution. "
I doubt it's faster than a well designed sql implementation - they are comparing it to their old solution, which was probably a clunker. 100-150 database requests per minute is not much - but probably way more that a fitness center would do.
There is also MySQL, which can run on a mac - and that would do just fine as well. Stop the anti mac hate
! -
I had much difficulty with the upsizing wizard in Access. It may be because I was using Access 2000. However the Access 2003 version was also not satisfactory.
I found on the net and downloaded (don't remember where now) "Microsoft SQL Server Migration Assistant for Access" which, although a bit of trial and error to work out how it operates, did an excellent job of moving the tables through to SQL Server.
Worth checking out.
-
What version of SQL Server are you using? Im using SQL Server 2000 with Access as a front end and I can administer the database from within Access 2000. I'm not sure how it would work with SQL Server 2005 since it just came out and there is no version of Access that was written at this point that was designed to work with it.
-
Access 2000 upsizing wizard needs to have service pack 3 installed in order to work with any version of SQL Sever after version 7.0. I had to upgrade to SP3 in order for it to work with SQL Server 2000. It is probabally a cleaner solution to just use Access Projects in the first place and not use the upsizing wizard. You will need to use T-SQL syntax for your views(ie select queries) and stored Procs(ie action queries) instead of the Access SQL syntax. The advantage of using Access is that you can design your forms and reports in Access. The other way designing an app would require you to use .NET programming languages and forms to access your SQL Server.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply