03-14-12, 14:17 #1Registered User
- Join Date
- Mar 2012
Newbie here with a (hopefully) simple question
Hi Guys, I have recently been put in charge of arranging a database for my company. What we do is provide phone and satellite communication to remote locations.
We have roughly 2500 phone numbers that are usually recycled between users. The users sometime change their numbers and specific satellite kit several times a year. I am looking to create a database with the capability of the following:
1) by typing in the name of the client or the ID of the satellite kit I would like all information regarding the day it was set up, and taken down.
2) inform me of what phone numbers have been associated with this Satellite kit.
3) If I am created a new kit and require phone numbers, it will only show me numbers that are not in use or available.
The company also has a reserve list, meaning that is someone has a phone number we will hold that number for the person for 2 years from the last time it was used. Then after 2 years of not being used it goes into the available pool.
As of right now we are tracking everything in a very basic Excell spreadsheet. Many people have access to it, so needless to say human error has done some damage to this system.
So if the database has an automatic function that woudl place them in the available pool once the time has expired.
If someone could point me in the right direction as to the best software to get this accomplished, it would be greatly appreciated. I am fairly tech saavy however this will be my first database project, I would prefer something userfriendy.
Thank you in advance
03-21-12, 06:35 #2Jaded Developer
- Join Date
- Nov 2004
- out on a limb
its perfectly possible to do this with a file server DB such as Access, Filemaker, SQL Lite or even XBASE.
but the key is going to be a good design and unless you are reasonably experienced than that coudl be tricky.
fileserver db's are fine upto a few tens of concurrent users, beyond that you may need to look at a more capable storage system
in temrs of the front end, the user interface then Access and Filemaker can both handle this sort of thigns. you'd need to use soemthignelse ontop of SQL lite (which coudl confusingly by Access).
I'd have a read through on normalisation
Fundamentals of Relational Database Design -- r937.com
The Relational Data Model, Normalisation and effective Database Design
are pretty good founhdations
just to give you a flavour I think you are gong to need tables for
customer contracts (ie when customer has an active contract with you)
an intersection table which identifies what equipment is used as part of that contract, and what numbers are used on that equipment)
there's probably soem more tables required
having established the design you can then use queries to extract and manipulate the data required
so to find what numbers have been used with what equipment you go through the intersection table (the one that links equipment with a number)
to find un allocated numbers you go through your numbers table, and look for ones that are not in the intersection table or are but the contract expired 2 years ago or more.
its doable, but the question is are you of that mindset to take on board the design issues.I'd rather be riding on the Tiger 800 or the Norton