What you have to do to make perfect ms database? (1 Viewer)

lacampeona

Registered User.
Local time
Today, 20:29
Joined
Dec 28, 2015
Messages
392
Hello
I am creating my database and I want to create perfect database. What is perfect database?
Can you experts suggest me what is nice to have, what is bettter to avoid when you want to create perfect database? Is possible to manage perfect database? How do you know that you make perfect database? :)
I will write some points that I think is good start in creating perfect database.

1. Login form, changing password
2. Audit Trail for users, audit trail on creating data
3. Nice and simple forms
4. Useful queries
5. Good loking reports
6. Exporting data to excel, imorting data in access
7. Making task reminders in Outlook
8. Locked data for viewing
9. Hidding tables, taskbar for users
10. Backup
11. Connecting the database to excel pivot tables
12. Sending emails
13. Animation on the forms?
14.?

thank in advance
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:29
Joined
Oct 29, 2018
Messages
21,358
Hi. When creating a database, I think it's perfect if it does what it was intended to do. In other words, each purpose for creating a database could be different from one database to the next, so what could be perfect for one, may not be perfect for another. Rather than focus on creating a "perfect" database, maybe concentrate first on creating a "functional" one. Just my 2 cents...
 

June7

AWF VIP
Local time
Today, 11:29
Joined
Mar 9, 2014
Messages
5,423
A 'perfect' db is one that accomplishes what you expect of it. With exception of items 3, 4, 5, everything you listed are enhancements and 'nice to have' and are not necessary for a 'perfect' db. There are some basic guidelines to follow to make process less challenging.

Don'ts:
spaces and punctuation/special characters in naming convention (use 'CamelCase')
reserved words as names
multi-value fields
lookup fields in tables

Dos:
data normalization ("Normalize until it hurts, denormalize until it works.")
backup
compact and repair
 

lacampeona

Registered User.
Local time
Today, 20:29
Joined
Dec 28, 2015
Messages
392
Hi. When creating a database, I think it's perfect if it does what it was intended to do. In other words, each purpose for creating a database could be different from one database to the next, so what could be perfect for one, may not be perfect for another. Rather than focus on creating a "perfect" database, maybe concentrate first on creating a "functional" one. Just my 2 cents...


Yes I agree. It depends on each purpose.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:29
Joined
Jul 9, 2003
Messages
16,244
Unless your goal is to learn how to be an MS Access developer, then the first thing you should do is find an off-the-shelf package that does what you want. If you can't find an off-the-shelf package that does what you want, get the nearest thing. If there's nothing available, then you need to be sure that you actually need an MS Access database. For instance if you want to access it from the internet, then it's probably not a good way to go, you might want to use MySQL and PHP. If it's going to be used on a mobile phone then you should probably look at at Flutter and Firebase. If you come to the conclusion that you really need MS Access because you need bespoke functionality not provided by off-the-shelf packages, you will have a better idea of what you want in your database, because you have seen examples of how people tackle problems you are likely to face within the off-the-shelf packages you have studied.

The next place to look is within MS Access itself:- See the Video "Setup the Northwind dB" showing how... There are many sample databases ready to download and use. However, bear in mind that many of the MS Sample dB's are designed to demonstrate the features of MS Access, and are not designed in what a developer would call a good way....
 

lacampeona

Registered User.
Local time
Today, 20:29
Joined
Dec 28, 2015
Messages
392
I am creating database for my work and i have very demand users becouse of that i write all that points that i think is good to have. i still not manage all the wishes but i am trying to create simple but funciontal database.
 

lacampeona

Registered User.
Local time
Today, 20:29
Joined
Dec 28, 2015
Messages
392
Unless your goal is to learn how to be an MS Access developer, then the first thing you should do is find an off-the-shelf package that does what you want. If you can't find an off-the-shelf package that does what you want, get the nearest thing. If there's nothing available, then you need to be sure that you actually need an MS Access database. For instance if you want to access it from the internet, then it's probably not a good way to go, you might want to use MySQL and PHP. If it's going to be used on a mobile phone then you should probably look at at Flutter and Firebase. If you come to the conclusion that you really need MS Access because you need bespoke functionality not provided by off-the-shelf packages, you will have a better idea of what you want in your database, because you have seen examples of how people tackle problems you are likely to face within the off-the-shelf packages you have studied.

The next place to look is within MS Access itself:- See the Video "Setup the Northwind dB" showing how... There are many sample databases ready to download and use. However, bear in mind that many of the MS Sample dB's are designed to demonstrate the features of MS Access, and are not designed in what a developer would call a good way....

thank you for your advice. i have to make good protection of data....so it not will be from the internet....
do you know how many users can use a ms database at the same time? i read on the net that maybe 50? is that true?
i will create front and end....
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:29
Joined
Feb 28, 2001
Messages
27,000
Error handling is important. If you have a "perfect" database you can still encounter an error for many reasons. If you have unhandled errors, your use will be exposed to the risk of being in a locked-down and totally unresponsive database. Something you NEVER want to have happen is for an error to occur in some routine and, due to lack of error handlers along the way, that error returns to Access itself. When you get the dreaded error pop-up that offers you debug, resume, or reset, there is NO correct answer that a user should have to make. By trapping errors in such a way that YOU get to declare a disaster rather than having Access declare it, at least you get to oversee the cleanup.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:29
Joined
Feb 28, 2001
Messages
27,000
how many users can use a ms database at the same time?

This is SO complex a question as to be very difficult to answer. Factors:

1. Raw number of users... I've managed 20-25 at a time out of a larger potential user list of about 75-80.
2. Speed of the network connecting front-end to back-end. Slow networks make TWO users at once almost untenable. Gigabit Ethernet makes it possible to support more than 20 users at once with little or no visible effect.
3. Size of your database (in terms of number of records in the main structural tables). This is a "playing the odds" consideration. If you have maybe 50 users and a table they must update that contains only a few hundred records, the odds of a "collision" are actually quite high. If that table is over 10k records, the odds of an update collision drop significantly. If they are appending rather than updating, the odds of a collision even get higher because for appending, they WILL be looking at the same area in the tail end of the back end file to put a new record. Further, the back-end host's memory and disk speed could become an issue because appending causes the file to grow. It also affects the virtual-memory size of the DB, which makes it depend on the size of the system's page/swap file.
4. Care of coding to minimize exposure - such as assuring that read-only operations assert "No Locks" and that updates or inserts or deletes assert "Optimistic Locking." Doing so minimizes how lock a lock must be asserted. It guarantees that whatever gets locked does so for the least amount of time possible.
5. Indexing of your main tables reduces the time it takes to find a particular record for updates and thus narrows the window of opportunity for a collision within a table.

The reason a DB can support multiple users is that as long as they don't "step on each other" they can peacefully co-exist. Item 2, 4, and 5 are about the probability of collisions when factors 1 and 3 are constant. Factor 1 increases probability as the user list grows in size. Factor 3 decreases probability if the DB grows in size, EXCEPT that doing an Append query (INSERT INTO) is not affected. It is always the same no matter how big the DB has grown.

There are other factors. For instance, speed of the disk on which the back-end is hosted; speed of the system on which the back-end is hosted, speed of the systems host the front-end. However, those last three are actually kind of hidden underneath the speed of the network. But suppose that you had a Gigabit Ethernet at work with an 8-core 3.8 x 64-bit file server and an SSD for user data. That system would run faster than the proverbial "bat out of Hell." Your front-end host would contribute by being faster so it is faster to release data and file locks. But USUALLY the network is so much slower that you don't notice the contribution of the last three factors.

There are also software considerations for things that take more or less time to load. Things like late binding come into play. To accurately say how many people an Access DB can support is therefore a lot tougher to answer than it looks.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:29
Joined
Jan 20, 2009
Messages
12,849
Error handling is important. If you have a "perfect" database you can still encounter an error for many reasons. If you have unhandled errors, your use will be exposed to the risk of being in a locked-down and totally unresponsive database.

Yes. However error handling is widely misunderstood with many developers claiming that every procedure needs to include an error handler. It often makes unnecessary clutter in a one line procedure that doesn't actually have a means of failure.

The fact is procedures without error handlers pass the error up the calling chain until either an error handler is encountered, which it then uses. or it reaches the top when it will throw an error if it hasn't reached a handler.

Make sure there is an error handler somewhere along the way.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:29
Joined
Jan 20, 2009
Messages
12,849
For me a perfect database wouldn't use and Access file backend but instead hold its data on an RDMS such as SQL Server and do much of its processing on the server.
 

Steve R.

Retired
Local time
Today, 15:29
Joined
Jul 5, 2006
Messages
4,617
if you consider $$$ cost as an aspect of your "perfect database"; use open-source databases (MariaBD) and associated open source code which tends to be "free".
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:29
Joined
Feb 28, 2001
Messages
27,000
Actually, I more or less agree with you, G. Every frimpin' little function and subroutine doesn't need an error handler. But at least every form needs one at the form level if not deeper. Ditto for reports, mostly for the "just in case" situations. What you want to avoid is to allow the error to pass all the way up the chain to Access itself because that is when you risk the dreaded error pop-up previously mentioned. As long as you have that much coverage, then you spare the users the need to know the right answer for a database they didn't design.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:29
Joined
Jul 9, 2003
Messages
16,244
Every frimpin' little function and subroutine doesn't need an error handler.

I suspect this is true Richard/Greg. However I had one terrible experience. I converted a database to the Run-time Environment and it kept throwing errors, and when Runtime throws an Error, it just crashes! There's sod all you can do... To track down this Error, I spent several hours going back through and putting error checking in all of my functions. I realised that it would be much simpler to have a function pattern/template stored in the "Insert File" section of the database.

Using the Insert a Function Template (with Built in Error Handling)



I have several boilerplate function templates, one for a standard function one for a subroutine one for SQL statements, one for records-set loops and more. Whenever create any of those particular items I grab the template and Away I go! Every single one with error code. Whether it's necessary or not, I'm a lot happier knowing that if I have to upgrade to run-time I'm in a far better place than if I hadn't put the error checking code in.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:29
Joined
Feb 28, 2001
Messages
27,000
You might recall a previous post I made where I talked about having "template forms" with a lot of the infrastructure already in place. Part of that included a pre-defined error management routine in a general module that I could call for error logging. Then all I had to do was put something like LogError Me, "Commit_Click", Err in order for the time-stamped error event to be fully recorded in my audit file. It would look at the ERR object so that it would have the error number AND the text. But it also would tell me where on the form the error occurred if I passed in a location string. When that subroutine call returned to me, I was able to then decide what to do about the error event, which USUALLY was to just blow it off by calling me RESUME xxx_Cleanup or something like that. So having copious error handlers was painless and didn't really cost that much anyway. Not that I would ever have taken the extreme of "a handler in every routine" but I had a handler behind every one of six or seven button-clicks that were common on each form and every form's _Open, _Load, _Current, _Unload, _Close, _AfterUpdate, and _Error routine. Therefore, if someone reported an error to me, I would be able to look at the error log and see what they had been doing. The users were also aware of the error and were automatically advised to check the record they had just updated because an error had been detected.

The users didn't call me because they knew I would find out immediately anyway. The LogError routine triggered an automatic notification to me via a CDO message so that I would know to look (and WHEN in the audit log I would see the error.) Between the button-click logs and the error events, I would usually be able to see that "{timestamp} User RWH, Form OneServer, clicked Commit for server RHSWEB31, Server ID 432" followed by "{timestamp} User RWH, Form OneServer, error 13, type mismatch, in Form_AfterUpdate". At that level of auditing, I was able to figure out code pathing right away and identify the culprit. Within perhaps three months after the launch of the app, that level of audit logging and event logging enabled me to reduce error messages by 95%. The next three months, I got rid of even more, to the point that for several months at a time I would get NO error notifications. So while I didn't have error handlers in every little routine, I had them in every BIG routine.
 

lacampeona

Registered User.
Local time
Today, 20:29
Joined
Dec 28, 2015
Messages
392
Hello

thanks to all for all your answers

one question....how i make ms database to mysql? what I need?

thnaks
 

lacampeona

Registered User.
Local time
Today, 20:29
Joined
Dec 28, 2015
Messages
392
Can somebody explian me more about MariaDB...
lets say i create database in MariaDB...how i will transfer that db to my work? in my work i have very strong criteria what people can use ..

hmmm?
 

Users who are viewing this thread

Top Bottom