Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2013
    Posts
    18

    Question Unanswered: Small Business DB advice.

    Hi All,

    I'm a newcomer to databases, but I'm moderately competent with Excel and VBA. I'm hoping you can steer me in the right direction in terms of:
    * What type of database to use? (i.e. Access vs SQL relational DB vs document based such as MonogDB)
    * Front End options for said DB.

    I'm not sure exactly what information you need to advise me, but here's some background information that I guess is pertinent. I've been tasked with providing better multi-user access to our companies data. Currently it is all be done using a system of spreadsheets (some linked to each other, some connected via VBA and some are shared workbooks).

    Database Purpose:
    * Tracking orders we place with suppliers.
    * Tracking details of the services we provide to clients.
    * Tracking changes or cancellations made in either of the above cases.
    * Reconciling financial records (i.e. comparing our records of active/cancelled services against billing information from our suppliers - so principally checking that dates and amounts correspond).

    Estimates:
    * No more than 25 simultaneous users.
    * Possible heavy use in future (i.e. automated data requests from software programs every X number of seconds, maybe every 5 seconds), but not heavy use at present.
    * Current server is MS Small Business Server 2012 and runs Office Professional 2013, it is unlikely that we will move from SBS.
    * All users access the server via RDP over WAN Links (private IP, not internet tunnels except in very rare instances).

    Existing Data:
    * All held in a tabulated form in Excel workbooks.
    * Unique key is our order number (it has a 1 to 1 relationship with other information that we record).

    Requirements:
    * The database needs to work for users trying to access it over private IP WAN links (i.e. relatively slow and high latency links compared to a LAN).
    * The data needs to be able to be imported and exported to/from MS Excel (preferably with options for which fields, rather than just a whole batch of data, such as an entire table).
    * The database needs to be able to have user level security (i.e. each individual - or group of individuals - needs to be able to be assigned 'rights' for what data they can see, read and write).
    * The database needs to avoid conflicts (i.e. Deal with two users that try to change the same piece of information basically simultaneously).
    * As time goes by, the information we capture for each order seems to need to grow (i.e. the number of 'fields' required for each order is not known or fixed in the future).

    Preferences:
    * The data should also be accessible via web based requests (http or https)
    * Automatic history or change tracking for each value (i.e. if a value is changed, then it time stamps when that occurred and ideally who by, without the user having to input the information).
    * Cross platform software is preferred, but certainly not essential.

    Any advice you can give me would be much appreciated

    Cheers,

    Chris.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    theres lots of issues here
    first off to store the data
    you probably need to go down a server route, whether thats SQL server, MySQL, Postgres or whatever is up to you. the reason. the requirement for the numnber of on line users. file server db;'s such as access start to trip up somewhere near that level, they are also not clever running over WAN's. bear in mnd that Access is both a database AND a front end, you can develop stuff in Access that uses a server backend, you don't have to use the in built database (which is actually called JET)

    you could develop the app using say Access as front end tool talking to either JET to SQL server Express. but you would need to examine the limits of SQL server express.

    as to how you access that data that depends on lots of thigns (the skilss and experience of the developer(s)
    whether you want it to run on a local internal network or over a public webserver or both.

    i owudl strongly recommned that you ditch any ideas of usign excel to store data, its fine for local copies and data manipulation but spreadsheets are frankly crap at data capture, storage and maintaining data integrity. if you do go down a db rtoute then thats where you data resides. by all means copy out to spreadhseets but never accept from spreadsheets.

    the rest of your requirements should be met by the physical design
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2013
    Posts
    18
    Hi,

    Thanks for the advice.

    The intention is to move the data from Spreadsheets, into the database. As you say, Excel's role in life will be for analysis of data read from the database, I've given up on the idea of using Excel as a front end.

    I'll look into SQL server and DB2 as a backend, see how painful that learning curve will be.

    What are the great advantages to using a 'NoSQL' or document type database?
    As far as I can tell the main benefit to it is that it doesn't fix the number of fields per record at the time when you create the database...is there something vastly more significant to them than that?

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Doc-type databases are great for discussion threads and workflows. They're not so good at solving relational problems.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Document type databases (any kind of Big Data) are great for discovery. If you don't know quite what you have or what you want to get from it, they are the tool of choice. Not needing to have a schema when you start is a huge advantage for this kind of problem.

    When you are trying to solve a problem that you can define, thinking in terms of things that you need to know about and the measurements that you need to know related to those things indicates that a conventional database (Relational, Network, Hierarchical, etc) will be orders of magnitude more efficient.

    A general rule of thumb is that a structured database can solve any problem that you can completely define, while an Big Data database will require a thousand times the hardware of the structured database but it can solve problems that you aren't able to define when you start.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Sep 2013
    Posts
    18
    Okay, I understand their purpose now, thanks.

    While I'm still on the subject of database types, I have also run across various types of 'OLAP' databases. From what I understand, these are essentially 3D spreadsheets. Now, in my experience in Excel, anything that 'needs' to be 3D can be also done with a sufficient amount of 2D sheets and a bunch of linking. '3D' is not so much required, as just more convenient and often computationally efficient. Is that essentially true for OLAP databases as well when compared to a relational database?
    (i.e. they're a more efficient way of analysing related data than having a whole heap 2D tables controlled by conditional flag and relationships etc)...

    I'm not certain, but I believe SQL server allows you to essentially have both relational structures and OLAP (hypercube) structures within the same database, is that correct?

    Further, do I really need to care about OLAP tables as a relatively small business with relatively small data amounts, or does OLAP really only make a functional difference once you start talking about massive amounts of data?


    On an Access specific topic:

    Does Access lock/save by record/field at a time, or by an entire record at a time?
    (In essence I'm trying to understand whether a conflict occurs only if two people edit the same 'cell' - i.e. record/field intersection - at the same time, or if having two people edit different fields of the same record at the same time would also be a conflict).


    Thanks all for the help!
    Last edited by Simple_One; 10-02-13 at 07:57.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    access can lock data using one of four appraoches
    no locks
    table lock (no one else can alter data (commit changes) whilst someone has a table lock
    page lock (no one else can alter data (commit changes) to that gorup.page of data
    row lock (no one else can alter data (commit changes) to that row
    there is no concept of locking a 'cell' or in db terms a column

    if you think thee is a risk that multiple users may want to edit the same row concurrently you need to build that into your form design
    so you'd need to allow users t make changes
    before committing changes re read the row to see if other users have made changes
    apply your changes to these changes and commit the merged data

    as you are currently using excel you'd have to ask yourself the question of how likely is that going to happen. at present only one person can changes data in an excel spread/worksheet, others can have read only copies but they cant' make changes
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2013
    Posts
    18
    Funny you mention that, I specifically ask because we often encounter conflicts in the shared Excel workbooks due to people trying to edit the same information within the saving timeframe. It's exacerbated in Excel (compared to how I think it works in access) by the fact that changes don't autosave as often (only every 5 minutes or so automatically in the shared workbooks), whilst in Access it would be done as soon as any specific records data entry has been completed.

    I'm interested in what you mentioned regarding the form design though, could you point me in the direction of something that discuss the method/concept of form design that you are talking about?
    Apologies for the request to be spoon feed, but I'm not yet sufficiently up to speed on the terminology of Access or DB's to be able drill down google search results to the things I'm after in anything approaching a time efficient manner. If you can't, that's fine also, no doubt I'll turn something up eventually

  9. #9
    Join Date
    Sep 2013
    Posts
    18
    Well, some research has answered this:

    "* Automatic history or change tracking for each value (i.e. if a value is changed, then it time stamps when that occurred and ideally who by, without the user having to input the information)."

    I've been watching the Lynda.com videos (Access 2013 Essentials or something like that), it seems the answer lies in what they call 'data macro's'. These at least allow the recording of a date/time stamp very easily, and I can probably work out how to capture the user name in VBA as well given a little bit of Googling. Glad to know this is (at least partly) possible

    I do have a question about it though:
    Is there a way to enforce a standard timezone?
    (so rather than having it capture whatever the time is set to on the users PC, it captures either an enforced timezone, or else the time on the server - it will probably do the latter if opened via RDP, but if a user opens the access file locally via a mapped network drive, then it might stuff the log up since we have people in different timezones).
    Last edited by Simple_One; 10-03-13 at 05:49.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you need traceability and are usign Access then you will have to roll your own, its not an inbuilt function of some server db's. do a google on audit trail / audit log in access

    Acces isn't a client server, its a file server. there is no concept of a central server. effectively you share the file on a server disk and each individual access application gets and process the data it needs. on a client server (SQL server, MySQL and so on there is an application that runs on a remote computer that handles data requests and parcels out data as required.

    one thing to be aware of is that Access timestamps are the timestamp of the machine writing that row, not a single centralised time. if you writer tiem stamp data on a client server db then the central application handles the time data, in Access each individual computer writes its time on its rows, ao a file server timestamp has far less reliability and may not hold up in a court of law if minute/second accuracy was important.

    to enforce a standard time zone
    ms access timezone
    but bear in mind VBA (the language inside Access) isn't VB and isn't VB.NET so yu ned to make certain you are referring to the correct help
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Sep 2013
    Posts
    18
    Great information, thanks, I'll do some Googling.

    In the meantime, in relation to the timestamp, would I be correct in saying the following:
    If multiple users from different timezones use the Access database via an instance of Access opened with an RDP session (the RDP session being to the central server that hosts the Access database file), then the time stamps would be consistent since they're based on the time of the central server?
    (I assume this would be true because RDP sessions runs an instance of Access local to the central server, irrespective of the users location or timezone....)
    I can see that the timestamps would certainly go astray if users run the Access instance locally on their machine, but open the database file via a mapped network drive.

    Either way, this is an issue that is relevant to us, I can't enforce the fact that the Access instance should only be running within an RDP session on the central server (i.e. I can't prevent people connecting to the accdb file via mapped network drives and running a local instance of Access on their machines instead).
    Sounds like a good reason to look at using SQL server or similar.


    So my other question on this subject:
    If we used SQL server, running on a central server with people connecting to it using an Access front end (running locally on their PC's), would I still encounter the problems of local timezone differences; or would the fact that the DB engine (SQL server etc) is running on the server be sufficient to make the timestamps consistent, even though the 'front end' is being run client side and is seeing a different local time on each of the user's PC's...

    Sorry, I've probably worded that poorly, but I hope you can see what I'm trying to ask.
    Last edited by Simple_One; 10-03-13 at 07:05.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Deepends
    If you use a server backend then probably yes, depending how you develop the system
    If you use the default data store in access its the local machine.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Sep 2013
    Posts
    18
    Nice choice in bikes by the way, got my eye on a Tiger 800XC to stable alongside my Daytona 675

    I'll set up a little experiment over the next week to confirm how some of the timestamp issues do respond when working via a network drive or when using Access only as a front end. I'll put the results in this thread should anyone else find the information useful.

Tags for this Thread

Posting Permissions

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