Can I have a little advice please? (1 Viewer)

[holykimura]

Hi

I have been scratching my head over whether i am right in using an Access Database or not. I have never really made a fully functional database before and am wondering whether what I want to achieve is complicating an existing method or not.

Currently we are using a spreadsheet to record information on upgrades and patches we apply to schools, and other machines in the office. My brief experience of Access has given me an insight on how a Database works as it reduces the need to duplicate data. This is why i think a database would be a better solution.

The current spreadsheet allows us to do the following:

• Put in a tick and a date next to a school that has been upgraded
• Put the name of any patch that the school has had and what date it was applied
• Put the names of any office PC's and Laptops that have been upgraded and what version of upgrade they have had
• The name of the person who carried out the update
• Enter any notes that need to be added such as any problems encountered, patch information etc..

Each of the schools and PC's/Laptops have different SQL databases (Releases) that we upgrade using third party software. Each of these can have different types of databases and more than one in some cases. Occasionally it is necessary to apply patches to schools, laptops and PC's .Each school is upgraded remotely using an IP address. Laptops and PC's are based in offices/rooms



I have carried out some Normalization today and came up with the following Entities:


School {ID, Address, Tel, Email, URL, Username, Password, IP_Address, School_Type_ID*, Database_Type_ID*,Database-name_ID*,Release_ID*, Patch_ID*}

School_Type {ID, SchoolType}

Database_Type {ID, Type}

Database_Name {ID,Name}

Room {ID, Name, Location}

Laptop {ID, Database_Type_ID*,Database-name_ID*,Room_ID*,Release_ID*, Patch_ID*, }
PC {ID, Database_Type_ID*,Database-name_ID*,Room_ID*,Release_ID*, Patch_ID*}
Release {ID, Release Date, Notes}

Patch {ID, Release Date, Patch_Notes}
Employee {ID, Name}



I have experience of creating tables, forms, some basic queries and reports.



I would like the database to be a functional database linked to a switchboard for one user to enter in information. The database should allow the user to print off reports about what has been upgraded ,when, by who, and which databases were upgraded. Other users should be able to access and search for schools and be presented with school information in a form to add more data if necessary or to update a record.





My questions at the moment are will the Database be too complicated to design for someone with my experience?



And if not do the entities look correct or can they be Normalized further? or even amended?

 

[plog]

My questions at the moment are will the Database be too complicated to design for someone with my experience?

No, you should be fine. The simple fact you've tried to do this on paper before jumping in with one big table then posting problems, makes me confident this is something you can tackle. It will take a lot of research and questions for us to answer here, but you will ultimately be fine.

And if not do the entities look correct or can they be Normalized further? or even amended?

It looks very good, but I see some issues:

1. Laptop and PC tables should not be seperate tables, but one table (i.e. 'Computers') with the same structure that they have now, with an additional field 'ComputerType' which would hold if the computer was a Laptop or PC. You shouldn't store data in table or field names that could be put instead into a field.

2. 'Name' and 'Type' are reserved words in Access and shouldn't be used as object names. I'd rename your fields that are currently named these using a prefix that relates to the table. For example, Employee.Name would become Employee.EmployeeName and Database_Type.Type would become Database_Type.DatabaseType.

3. There's something wrong between Database_Name and Databast_Type tables. One probably needs to link to the other, then instead of having the ID of both in a different table (i.e. School). I'd need to see data from both these tables to give specific advice, but I'm pretty sure those tables should have a many-one relationship.

4. Same issue as #3 but with Patch and Release probably. It may also involve Database_Type and Database_Name, depending on how those tables relate to each other.

5. Employee seems to be a stand a lone table. Where does it link or what links to it?

6. Should IP_Address be in the School table or the Computers table? What is it really assigned to?

7. School probably shouldn't have Database_Type_ID*,Database-name_ID*,Release_ID*, Patch_ID* fields. I think that data would best be attached to a computer, not a school.

8. Offshoot of #7--how do you tell what school a computer is at? You know what room its in, but neither Rooms nor Computers link to schools.

I know I raised a lot of issues, but like I said, you can do this. Don't get discouraged, post answers to my issues (preferably numbering them so we can keep track) and we will get the proper table structure.

 

[gemma-the-husky]

sorry - this won't be what you want to hear.

the thing is - using access is quite different to excel.

excel is relatively easy to do, untaught. Generally you can get results with no previous experience - and just a bit help here and there.

Access just is not like this at all. Databases need careful planning, and a real understanding of how to get data into them, and out of them. Unlike a spreadsheet you hardly ever "see" all the data in a database. you ask the database to summarise the data for you. For instance, you should never (in normal circumstances) open a table directly. All the interaction with tables should be through forms and queries. Even as database designer you do not use tables directly.

Unlike Excel, which you can use without macros, it is virtually impossible to achieve anything useful in access without code. Access code, although it can be written as "macros" is very different to an excel macro. It is not a stored series of commands. It is real programming. A database macro is just a shorthand way of writing some code.


more than that, access is just so powerful, you generally tend to add a lot of code to LIMIT actions that users can take.

If you want to get results in a business setting, and have no previous experience, I would say you just have to get some professional help to guide you. If you are in a school I appreciate this may be difficult, but I really do think a beginner has virtually no chance of achieving decent results, without a lot of effort, that will probably eat into other jobs you need to do.

 

[holykimura]

No, you should be fine. The simple fact you've tried to do this on paper before jumping in with one big table then posting problems, makes me confident this is something you can tackle. It will take a lot of research and questions for us to answer here, but you will ultimately be fine.



It looks very good, but I see some issues:

1. Laptop and PC tables should not be seperate tables, but one table (i.e. 'Computers') with the same structure that they have now, with an additional field 'ComputerType' which would hold if the computer was a Laptop or PC. You shouldn't store data in table or field names that could be put instead into a field.

2. 'Name' and 'Type' are reserved words in Access and shouldn't be used as object names. I'd rename your fields that are currently named these using a prefix that relates to the table. For example, Employee.Name would become Employee.EmployeeName and Database_Type.Type would become Database_Type.DatabaseType.

3. There's something wrong between Database_Name and Databast_Type tables. One probably needs to link to the other, then instead of having the ID of both in a different table (i.e. School). I'd need to see data from both these tables to give specific advice, but I'm pretty sure those tables should have a many-one relationship.

4. Same issue as #3 but with Patch and Release probably. It may also involve Database_Type and Database_Name, depending on how those tables relate to each other.

5. Employee seems to be a stand a lone table. Where does it link or what links to it?

6. Should IP_Address be in the School table or the Computers table? What is it really assigned to?

7. School probably shouldn't have Database_Type_ID*,Database-name_ID*,Release_ID*, Patch_ID* fields. I think that data would best be attached to a computer, not a school.

8. Offshoot of #7--how do you tell what school a computer is at? You know what room its in, but neither Rooms nor Computers link to schools.

I know I raised a lot of issues, but like I said, you can do this. Don't get discouraged, post answers to my issues (preferably numbering them so we can keep track) and we will get the proper table structure.

Hi and thanks for the positive reply i have taken on board what you said in #1 and #2 In answer to your questions:


#8 - Schools have Servers, we use the IP address to remotely access the server, names of the server are available. School Computers pull the updates and patches from the server.

#6 - Currently we do not update the PC's and laptops (Computers) remotely as these are upgraded differently to schools as we use a third party software to authorise patches and releases to schools Therefore the Computers are manually upgraded at present.


#3 Each school has either a primary or secondary database but not both. However they will also have other database types called FMS and Discover. The Computers in the offices however are different as they will have the primary and secondary Databases as well as the Discover and FMS databases. There is only one type of Discover database.
Here is where it gets complicated and where I really began to scratch my head. The FMS databases can have several versions such as FMS1, FMS2, etc….
Some but not all computers need FMS databases. However the Computers that do have the FMS databases may need 1 or more updating.


#5 The employee table is included in this as I thought it would be useful to know which one of us upgraded the school or computers.


#4 A release is issued 3 times a year; Autumn, Spring and Summer. If there are any problems with these releases then a patch is issued for that release. There are also times when a patch is issued to resolve a specific school’s problems



I hope this makes sense enough for you to get a better idea of what I’m trying to create. I have tried to include as much information as possible but didn’t want to bore you with pages of information. I look forward to your reply. Thanks gain.

 

[holykimura]

Hi Dave, thanks for your reply, it is for a business setting. The reason why I posted here was because I want to create the database myself but it is not as straightforward as i thought. Hence why i am looking for some guidance.

 

[plog]

I think you should give your structure another try. Don't try to accommodate everything all at once. Either work top down or bottom up. From what you've told me Schools are the highest level object and Patches are the lowest level object. Now you need to build the objects between those 2. When adding something always use the term 'belongs to': patches belong to releases, releases belong to databases...

One tip--from one level to another you only need the next level up's id in that table. Patches would have an id of the level object above it (releases) but not vice versa, nor would patches contain any level above that.

 

[gemma-the-husky]

h

this sounds like a complicated project.

i think you will only get so much help for free, and it will take you a long time to understand the advice, work out which advice is better than others, and build your database, i would have thought if you do not have the skills, you need to get some professional assistance on this. not free, but it will shorten your development cycle.

 

[holykimura]

Hi both, thanks for your replies I think I would need to create two different databases and like you said this project sounds quite complicated. I will stick with the Excel solution I have for now but will ask to go on some Access training in the future.