Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2007
    Posts
    5

    Unanswered: Creating a new database

    Hello all,

    Based upon some recommendations by a couple of dbforum users (healdem & georgev), I'm going to be looking at using MS Access 2003 for creating a database of users in the company I'm working at.

    I'm part of the IT support group at a company with approximately 80 users (and growing). We're in the process of migrating from Windows 2000 to Windows XP in the next few weeks, and I figure this would be a great time to create a database/inventory of what we have in the office.

    Basically, I'd like to create something that would contain user information, including Name, Seat location, Phone number, Computer Name, Specialized software, Hardware Spec (CPU, RAM, OS, etc), and so forth.

    • I would like to have this easily accessible in terms of locating information (for example a user calls for assistance and I can look up their computer info and know either where to walk to or which computer to remotely access)
    • Easy to update/modify as things change (people leave, new hardware/software installed, etc)
    • Easy to search and display specific information for reports to HR/Management
    • I suppose making me a cuppa coffee would be too much to ask...?


    How do you recommend I go about starting on this? I have almost no experience with Access so any advice on how to begin would be most appreciated. Or if you have any online resources that you feel would be very useful, let me know as well!

    Thanks in advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Design before you go any further

    First thing to do with any database (IMO) is to design your table structure!
    Ever heard of normalization?
    I'm sure someone will have some lovely links for you on normalization, but why not head to google in the meantime?

    Perhaps if you posted sample data for, I dunno, 3-5 PCs, we could take a look and give you some advice.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    brave choice cheerfulnut.. and I hope you are still cheerful in a short time.

    normalisation is the key concept behind table design. its main drive is to make certain that you store data appropriately or atomically to use the jargon. Rudy/R937 (one of the long time mod's on this site) has a great piece on normalisation and table design.

    In my view one of the major drawbacks of a tool like access is that its almost too easy and too tempting to dive right on in and start designing your app. Even for experienced pros it can lead to problems later on. the more time you spend on the table design the better your chances of getting a stable app in the long time.

    to kick off the process think about what you need to do, read the realtional page on Rudy's site, and scour the web for help.

    use a pencil and paper.. resist the urge to fire up access for as long as possible.

    if you are just starting out consider using a naming convention, personally I use something lke capitalising different words eg DeviceType, use a column ending in ID to identify autogenerated fields, use abbreviations eg Desc for Decsription, TelNo for Telephone Number. above all make sure your fields are self describing (dont use field1, field2...) they should make sense to you (and others) when you read them in your forms, reports or whatever

    avoid using spaces in your names, eg use TelephoneNumber in preference to Telephone Number
    avoid using SQL reserved words, although access is relatively savvy at resolving such issues other db's arent. so use DateBought, Date Commissioned in place of Date.


    to give a brief flavour of what Id expect to see.......
    tblDeviceType 'contains details about different device types (eg printers, computers, routers, switches, pritner switches etc....
    DevTypeID 'autogenerated number and Primary Key
    DevTypeDesc ' describes the group

    tblDevice ' contains details of all devices (eg
    DevID ''autogenerated number and Primary Key
    DevTypeID ' referes to value in tblDeviceType which identifes what type of device this is
    DevDesc ' device description

    you main design problem, that I can forsee, is going to be trying to model the different configuration of the devices (eg which ones have which memory, which graphics)
    which devices is associated with which other device (eg which monitor is connected to which PC, which PC is connected to which router port, and which IP address. Bearing in mind that these can change at the drop of a hat and you may not necessarily be told who or when the deed was done.

    good luck with the project, but please bear in mind this forum is here to help with problems, if contributors feel like helping, they are not paid for it and it depends on their workload or interests as to whether they reply or not. Its not a forums for teaching you how to use Access at any level (user, designer or whatever. That doesn't mean you should be afraid to ask questions here, just dont get to rely on a helping hand being available at all times when you need it.

    The more you do yourself, the more info you give the better, read the Access FAQ on the first page of this thread. the more you show you've tried to do somethign the more chance you wiull get a reply. Many students effectively post here saying Ive got to complete my project assignment tomorrow can someone do this for me (they may even get cute and wrap it up in a sort of business problem narrative) but usually they get smoked. I forget who it was but you will "learn more by doing" than by being given answers on a plate

    good luck
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem
    the more you show you've tried to do somethign the more chance you wiull get a reply
    Amen brother.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2007
    Posts
    5
    Whew, that is one long post healdem! Give me some time to read (and re-read) both your post and (since both you and georgev recommend it) the link you gave on normalization (not something I've ever heard of before..)

    I've read advice about planning the design of the database beforehand, and will try to do so.. hopefully I won't fudge it up too much. I will write again once I've read and tried my hand at something.

    Thanks

  6. #6
    Join Date
    Dec 2004
    Posts
    37

    Question FAO Heraldem

    Hi,

    I occasionally need to put databases together and thought the advice about Naming Convention was spot on.

    I always seem to start off with good habits, start to lose them as time goes on and then have to return and undo any short-cuts I made and then have the problem of sorting out any queries, forms, reports etc that I made using the bad habits.

    One thing. If I have two tables linked on their Key Fields with a one to many relationship, is there a recognised naming convention to show the link? I have in the past ensured that the fields that are linked in this way have the same name, but this has caused me a number of headaches.

    Or is it not the general rule to have a 'tag' in the name to show that it forms part of a relationship?

    Many thanks
    Mike

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't think there is a hard and fast rule on naming conventions as their has been with the introduction of Simonyi's Hungarian notation on coding

    using a specific tag to identify if its a FK as a convention sounds attracive, but I can see problems if a FK has to be retrospectively defined. rework is always expensive, and possibly unlike others I find sometimes y'just have to bite the bullet and revisit the table design.

    generally I tend to use the same name (if possible in the "child" table), failing that I'd use as descriptive a term as possible) in most cases if there is a descriptive way of inferring the datatype Id try to include that, but its not always possible (eg TelNo). if it was a DeviceType Id probably define it as DevType if it was alphanumeric, or DevTypeID if it was autogenerated and use the same name in any other table that used that column.

    eg for an employee table
    Id expect the PK to be EmpID
    and the employees mananger to be ManagerID or MgrID

    like all naming conventions they are only useful if they are meaningful and easy to understand. Although I tend to use hungarian notation in code, I wouldnt want to use it in column design as it obscures the meaning. its fine for code where you can immediately see the datatype from the declaration.

    But thats not neccesarily required in defining tables: the aim of a convention should be to make the data element being stored there to be transparent. bear in mind some places allow users to write their own reports & queries and the like so having a something which reads as English is a huge productivity boost. it also stays reasonably honest within the original design philospohy behind SQL that it should be structured and easy to read.

    I wouldn't impose a naming convention just because thats the way "I" see it, if the customer has their own terms then Id expect to use their terms in their database, after all its their property it should reflect their lingo.

    One of the key drivers behind a convention should be transparency, it should be possible to identify the purpose of the column from its name. some design shops would insist on say prefixing the columns in table with something
    eg EmpTitle, EmpForeNames so as to distinguish them from other tables which may have similar column name (eg customer CustTitle, CustForeNames OR supplier SuppTitle, SuppSurNames. personally I dont think thats neccesary, but thats my personal preference, if you are bringing tables together you can always use an alias as part of the SQL declaration

    to me the give away is that usually a FK is an autogenerated ID column in the parent table, where Iwould tend to ass an ID suffix (eg EmpID). So if I see a column in a table definition which has an ID suffix and isnt autogeneratred in that table that alerts me to it being an FK.

    BTW the way you are not the only sinner starting off with good habits and finding after a while they go to pot.. been there got the (XXXXXXL) tee-shirt on far too many occasions myself.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2006
    Posts
    265
    Just as or even more important, is understanding what your are trying to acheive and how this information needs to be presented or used. For instance, how many of your PC's are fast enough to run XP, how many do you need to replace? Or do the PCs have enough RAM, 512MB minimum for XP, and of those, what type of RAM is it. What is your database going to contribute to the organisation's business model?

    Whilst I agree that naming conventions are important, a badly strucutured database is not helpful, no matter how eloquent the naming conventions are.

    Always remember that reporting is one of the most useful tools to analyse information, dare I say it, sometimes you need to reverse engineer your database and a period a reflection is always useful.

    I would also add, don't be afraid of making mistakes, these forums are here when you get stuck.

    Good luck

    Simon

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by SimonMT
    Just as or even more important, is understanding what your are trying to acheive and how this information needs to be presented or used......
    absolutely.....

    to me the conversion of the business logic into a computer application is in reality nothing more than translation, if you know the language then the translation process is pretty straightforward. but you also need to understand the business requirement.

    fully getting to grips with the business logic is the key. whether thats through a formal Requirements Definition Document or Systems Analysis function or through 'matey' chats with the users doesnt really matter. far to often I see projects that were kicked of by people with a bit of time to kill, or a desire to get involved in development who are keen to start writing their own applications, sometimes they compelte the app, sometimes they break off part way through, sometimes they leave and the next incumbant can't make head or tail of whats been done. Access is especially bad for that as you cab get something that looks real purdy, real quickly however the innards are often absolutely pants).
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem
    ...Access is especially bad for that as you cab get something that looks real purdy, real quickly however the innards are often absolutely pants).
    Like a company that I built an application for: They payed over £3K for an application that looked good, but half the buttons had no code behind, and don't get me started on the table "structure".
    Needless to say, there was a small court case and then they came to me asking for a working app that they wanted to look identical to the non working one. Which brings me to the point: informal chats are not always what you want, make sure you get some things in writing so that when (you know they will) the customer changes their mind without wanting to pay for more development, you can wave your specification at them and smile
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2007
    Posts
    5
    Ouch! Sorry I've missed all these replies guys.. I checked a couple of days after my last post but then let that slide as things have been really busy at work these days.

    NOW however, I'm on a 1 week vacation visting family in Canberra so I had a little while to kick back and start going over the db ideas again.

    To cover Simon's point though, the db I have in mind is really something I was thinking of doing on my own time (even my coworker is saying not to bother with it when I tried discussing with him) and not something the company is requesting. The thing is, we currently have an excel sheet with a hodgepodge of info like user name, phone number, (maybe) computer spec, and computer name. This has just gotten more and more messy over the past months and I was thinking a db in Access would help keep things tidy(er).

    This will be something I do on my own time (whenever I have any - a relocation of my parent company w/ 400 users is coming up and I will be assisting with that once vacation is over ) so when I have questions they will most likely be infrequent and irregular.

    Thanks for the encouragement and advice, and I'll be lurking and posting as I (slowly) move along.

  12. #12
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    hi, Cheerfulnut,
    If your visit to rellies in Canberra implies that you are resident in Oz, perhaps I can give you a bit of help offline (I live in Melbourne) instead of taking up the valuable time of all these great guys in the forum. I have used Access ever since it first came out. I am semi-retired now but still supporting the databases of a number of local and remote clients. There is a process by which you can send me a private email through the forum if you like, to establish contact.
    Regards,
    Jim Wright.

  13. #13
    Join Date
    Sep 2006
    Posts
    265
    Being optomistic, still change is occurring purely from a practical point of view, this is actually a good time to get database in order, a freash start. So even if you do nothing about the database, bear it in mind during the move. Whatever you record could be used at entry information for your database. It is the only time I ever now conclusively where everything is!

    Good luck on the move.

    Simon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •