|
Sea_Sharp wrote: My thoughts were to restrict non-techie access completely from the database.
Ditto; the Access-frontend would link purely to readonly-views on their own version of the database; a simple restored backup of the production-database (on Sql Server). Then again, most people would not offer Access to an end-user and claim that it's the UI they should work with.
Sea_Sharp wrote: I found on the last project, having many people access the database at once resulted in many issues.
Yes and no. Access does it's job well, but it was never meant to store a lot of data and be accessed by multiple users simultaneous. A database-server is meant for that job, and Acces isn't a server-application - "just" a desktop app.
Sea_Sharp wrote: I guess my wording here was awkward.
Sorry for my tone/wording; it'll be equally (if not more) awkward (or hostile) at some points.
Sea_Sharp wrote: I know Excel can do it via exports but then it becomes the issue of everyone wasting their time trying to make the reports look professional, consistent, and
legible
..aaah, yes, users are resourceful.
Sea_Sharp wrote: As mentioned earlier I was completely unimpressed with the UI for Access's report builder but looking around I have not seen any alternatives that seem any better.
The Query-Builder looks scary, but is a very powerful tool; combine that with the reporting, and you have a "simple" UI - although others will certainly disagree there. Combine that with the free runtime-version of Access, and you got a UI that will be hard for the end-user to "break".
Sea_Sharp wrote: You should have seen how bad our MS Access db got corrupted just by having 2-3 people working on it simultaneously.
Yes, especially when they're mucking in the same tables. When moving to SQL Server, be sure to read up on locking and transactions - though the concept does exist in Access, it doesn't help to improve multi-user access. Locking is what keeps the data consistent in a multi-user db, but it also introduces a bottleneck.
Sea_Sharp wrote: I mentioned earlier I think it would make a lot more sense to do it HTML and CSS or XML and XSLT for professional looking reports to the client, and unformatted
spreadsheets for internal usage
You want an introduction to ASP.NET[^] or PHP[^]. You can easily read from the database, and have your CSS applied. You can modify the data and output it as HTML or XML. With or without transformations. And you could introduce new things as you learn; start with a basic formatted list that simply shows the content of a table. Next, create one with an extra filter.
It may take a bit, but there's always the forum to help 
--edit
Added hyperlinks
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy's answer is deep and clean, but I would add one more - don't do it alone. Not at the beginning at least. Hire some expert to help you with the design phase and put the project on the right tracks...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Like Eddy I am prejudiced to SQL Server, partially because I have been working with it for years but mainly because it has the most support resources on the interweb.
You are discussing multiple requirements, the primary on is to set up a database to store and manage your data, here I agree that you should get in a professional to design your data structure, it is by far the most critical component.
Next is the user interface, you need to build at least 1 application, your non tech access to the data and processes. You also need to create and interface for the existing Excel sheets into the data.
Lastly you need a reporting strategy, I would recommend SQL Server Reporting Services (SSRS) this can be set up as both server based or embedded in you application.
I believe you are in the position of a power user just dipping into becoming a developer and your tool horizon is limited to Office products, you might want to expand it to include a proper development environment.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am using below MySQL stored procedure to add a new job order into MySql table.
I would like to do the following modification in the code:
I want to add an additional parameter called param_max_auto_weekday
then I want to check the WeekDay of the param_job_order_date
and repeat the code until reaching param_max_auto_weekday
so if the WeekDay(param_job_order_date) = Sunday
then It should INSERT INTO job_orders for every Sunday in the week until param_max_auto_weekday
Here is the code... with Thanks:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_job_order_date_to date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11, 6), IN param_created_user int, OUT param_record_identity int)
BEGIN
INSERT INTO job_orders (customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_contract, contract_id, total_hours, created_date, created_user) VALUES (param_customer_id, param_cleaner_id, param_job_order_date, param_job_order_date_to, param_start_time, param_end_time, param_job_order_note, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
SET param_record_identity = LAST_INSERT_ID();
UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;
IF (param_is_contract = TRUE) THEN
BEGIN
UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_record_identity;
UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
END;
END IF;
CALL sp_add_event_log("JOBORDER", param_record_identity, param_created_user, "Job order was created.");
CALL sp_add_event_log("CUSTOMER", param_customer_id, param_created_user, "Job order was created.");
END
Technology News @ www.JassimRahma.com
|
|
|
|
|
That's not a question, that's work.
Did you run into any problems while adding the param? Why is the post here? Did you receive an error?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
yeah but my questions is how to start with it?
if today is 10th April 2014 and I want list of every Sunday until 10th April 2020?
Do you I need to loop?! what's the best way in terms of performance?
Technology News @ www.JassimRahma.com
|
|
|
|
|
I wonder, does MySQL only support lower case? That is almost unreadable.
I would do that differently, create a query based on the number of days to be inserted, and insert in one query rather than a loop.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am currently using stored procedures to do an action after my INSERT, UPDATE or DELETE.
I just wanted to ask for your advise..
do you recommend to continue using the stored procedures or start using triggers?
my simple example is an event-log table... I insert a row in this table if a row was inserted in employees table.
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
|
For logging purpose, Trigger is fine. Personally I prefer Stored procedure because you can't control the Trigger.
And Trigger Disadvantages[^]
thatrajaCode converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
|
|
|
|
|
Your link shows very important information. And from that information, I conclude that it may be a bad decision to use them for logging purposes: in a transaction rollback, also the trigger actions will be rolled back. I.e. when you log by inserting into a table, that will be rolled back, too!
|
|
|
|
|
Sorry for the delay. For beginners(yes he is), Triggers not suggestable. We can't control that. As Eddy said, we can't dubug too. Personally I use Stored procedure instead of trigger. But I rarely use triggers for tasks such as audit trail(During Login/Logout).
thatrajaCode converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
|
|
|
|
|
Jassim Rahma wrote: my simple example is an event-log table... I insert a row in this table if a row was inserted in employees table.
Can be done both ways. Triggers *could* be harder to debug though.
I cannot recommend anything, as that would depend on the requirements of the audit-trail. How does your "event log table" look, and what are you planning on doing with it?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Jassim Rahma wrote: or start using triggers?
As with anything it depends on the usage.
Jassim Rahma wrote: my simple example is an event-log table...
Might note that in my experience doing this is often driven by the need for audit logging and it is seldom sufficient as it ignores user, reason and fails to allow deletions to be handled well.
|
|
|
|
|
so in this case you pefer to do it through SPs?
Technology News @ www.JassimRahma.com
|
|
|
|
|
Hi,
Is is possible to create a trigger ON SELECT in MySQL?
If not, any other way to do it?
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: Is is possible to create a trigger ON SELECT in MySQL?
Only 3 events INSERT, UPDATE & DELETE. CREATE TRIGGER Syntax[^] & its Syntax and Examples[^]
Jassim Rahma wrote: If not, any other way to do it?
What do you want? explain clearly(ON SELECT).
thatrajaCode converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
|
|
|
|
|
Jassim Rahma wrote: Is is possible to create a trigger ON SELECT in MySQL?
No. That's why you added the second question, ain't it?
Jassim Rahma wrote: If not, any other way to do it?
No. You can fetch from a SP and add that functionality yourself. Triggers aren't meant as an audit-trail, that's hardly secure.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Dear all,
I'm currently trying to make a DB system scratch which I can use as backbone for my Qt application. Since I will store big amounts of data [photo RAW data] in there, I figured that a classic relational DB might not be the best solution. What I'm planning is now a classic relational DB [MariaDB] as persistent storage, cached by a No-SQL Database [MongoDB].
In the end the whole system shall be built up like this.
Here's a little additional explanation:
Persistent Storage Access
The Persistent Storage Access handles the access to the persistent MariaDB database which holds all the application data. It supplies the data used to build the Cache and if there are Update or Creation Queries, but also for Get Queries if the cache can't supply the requested item.
Cache Access
The Cache Access handles the access to the MongoDB database which is used to cache the access to the Persistent Storage. It contains a copy of the most recently added, updated or requested items.
Data Access API
The Data Access API coordinates access between Cache and Persistent Storage. If an item requested can't be found in the cache, the DAA gets it from the Persistent Storage and also adds it to the Cache. If an item is added, the DAA saves it into the Persistent Storage and also adds it to the cache. All this coordination is needed to take out complexity from other parts, e.g. the business classes.
I I'm in doubt whether the MongoDB will really give me any performance boosts, so if anyone could share their thoughts on what I plan here?
I will never again mention that Dalek Dave was the poster of the One Millionth Lounge Post, nor that it was complete drivel.
How to ask a question
|
|
|
|
|
The size of the database does not have very much to do with the performance. (Also remember that they have memory caching built in.)
The question is how you're going to use it.
The big weakness with MySQL/MariaDB is joins and aggregations with lots of data and a lot of tables. And I doubt that's how you're going to use it if you're having an image database. Fetching single items or ranges from an indexed table is actually where MySQL/MariaDB excels. Keep in mind that Google actually uses a (highly modified) version of MySQL.
My key rule for performance is that it's the amount of data you need to handle at a given moment that matters.
So tell us how you're going to use it first.
|
|
|
|
|
I have started an article[^] on the program I want to write.
The idea behind it that I can assign photos to albums - One photo can be part of any amount of albums, and can have any numbers of tags assigned.
Furthermore, the photos shall be versioned, e.g. there is the base (original) photo and several versions on which photoshop was applied.
Possible queries are (apart from the usual insert etc..):
- Get all child versions of a photo
- Get all photos for a given album
- Get all albums a photo appears in
- Get the parent versions of a photo
- Get tags assigned to a photo
- Get photos which have tags X & Y [...] assigned
- Get photos which have tags x & z assigned, but not Y
I will never again mention that Dalek Dave was the poster of the One Millionth Lounge Post, nor that it was complete drivel.
How to ask a question
|
|
|
|
|
I think you don't need the cache server, in your case it only adds to the complexity without adding much functionality.
Had you been working with large queries that aggregates down to small data volumes <edit>that are reused</edit>, it would be a completely different situation.
I also think you're a bit wrong about SQLite. It's a file database alright, but so are (almost) all databases. The problem is that it's a SINGLE file database, so when it has grown out of the harddisk(s) on the server you're in trouble. But the real problem it has is that it's bad with concurrency.
So assuming that you anticipate some growth I agree with your choice of MariaDB for this purpose.
modified 9-Apr-14 5:34am.
|
|
|
|
|
Jörgen Andersson wrote: So assuming that you anticipate some growth I agree with your choice of MariaDB for this purpose.
I definately anticipate a big growth, since the image raw data will be contained in the database - A single raw image data file is 20-30 MB easily.
I will never again mention that Dalek Dave was the poster of the One Millionth Lounge Post, nor that it was complete drivel.
How to ask a question
|
|
|
|
|
SQLite is file-based; but that doesn't make it slow. And no, it's not file-based like FoxPro where each object is a file. It's a friggin' fast single-file database, ideal for local caching.
You should also take in account that servers and local databases work quite differently.
..and no, you should not need to cache the db-server (unless your caching the blobs locally). If it's that slow then you need to revisit your datamodel.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: SQLite is file-based; but that doesn't make it slow.
I know - What I think is that it becomes slow if I load and save big photo raw data frequently.
I will never again mention that Dalek Dave was the poster of the One Millionth Lounge Post, nor that it was complete drivel.
How to ask a question
|
|
|
|
|
Marco Bertschi wrote: What I think is that it becomes slow if I load and save big photo raw data frequently.
Why?
Databases (both local and server) are meant to handle data. That includes blobs.
Yes, modifying a blob might be slower than a file-system; especially if the blob is doubled in size. Still, I did not notice any delay when I hooked up the dokan-driver to Sql Server. That made my db with blobs accesible using explorer, and I created, opened and dropped the connection on every read. Reading a blob is done in 4k pieces. Copying a file to/fro the DB still happened at 40 MB/sec (the max for that driver). It was fast enough on my testmachine (with 1 Gb RAM) to simply unzip a large file IN the folder that was linked to a table.
Depending on your network, it may be very expensive to fetch a large blob (say 250 Mb on a 10 Mb network). In that case you mimick IIS - you fetch a picture once, and if it's fetched again you send a HTML 304.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
