|
You can size it like the following
2000 users
100 messages a day
Total = 200,000 message a day
Messages per year = 400 * 200,000 = 80,000,000
Why 400? Because precision is not needed. All of the numbers are just guesses to give a goal.
Then further what is the average message size? 100 bytes or 1,000,000. Obviously the second is going to be because they are transferring files and not just messages. So do you keep the files also.
One thing I don't see in your post is growth rate. Are you starting with 200 users and only expect them to grow to 2000? Or the market that you are targeting, at best, only has about 2000 users?
You also do not document retention rates. How long do you keep the messages? 1 year? 7 years (often sufficient for most legal/business reasons)? 20 years?
So what database do you need? Any really. Nothing you posted suggests a need for 'speed'. All you are doing is keeping it for future analysis. You could even just write it to files.
You also do not mention personal security. Presumably, excluding the analysis, the messages are not subject to random poking around by operations personnel so how do you secure that?
|
|
|
|
|
My main problem is concurrent database read/write capability. I'll delete "seen" messages every month. I think Sqlite is not good for this project. What about SQL Server? Or Mongodb?
|
|
|
|
|
|
Alex Dunlop wrote: My main problem is concurrent database read/write capability
Nothing in what you posted suggests that is a concern.
Each new message is just a new record regardless of how you store it. Even if you allow edits you should handle that as a new message rather than replacing the previous one.
Alex Dunlop wrote: What about SQL Server? Or Mongodb?
Not a matter of whether those would work but rather if they are overkill.
When I looked up the transactions per second speed for Sqlite I did not see anything that concerned me for the sizing I previously posted.
|
|
|
|
|
Sqlite is meant as a single-user database. MongoDb is for documents.
Alex Dunlop wrote: users will be registered on the database for further analysis or detecting any criminal materials or misuses.
The answer is a clear no.
You might want to consult a lawyer, not a software-dev.
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Eddy Vluggen wrote: You might want to consult a lawyer, not a software-dev.
At least in the US and presuming a single company then users can have no expectation of privacy for company resources. Notifications to company employees are nothing more than a courtesy. Although perhaps also that they should keep the personal stuff off the company sites.
|
|
|
|
|
Assuming the database will be controlled by some hosted system that is only accessed by your backend system, then SQLite would be a resonable choice. See Implementation Limits For SQLite[^] for guidance on sizing.
|
|
|
|
|
If the SQLite database is accessed only by the backend, could it handle 500 read/write requests which are sent to the beckend at the same time?
|
|
|
|
|
That is impossible to answer as it depends on many factors. If you need specific performance levels then you would need to do some testing of your own.
|
|
|
|
|
Code4Ever wrote: could it handle 500 read/write requests
No sorry that is not a valid question based on your original post.
Exactly how are 2000 users supposed to generate that many messages at one time?
|
|
|
|
|
Through the this content
SQL Server - BLOB Import and Export[^]
and
How to store and fetch binary data into a file stream column[^]
, I was able to retrieve the blob from the FileImage column.
But it doesn't work for some records. In other words, it opens with an error for this type of file that has been fetched.
Table structure:
CREATE TABLE [dbo].[Attches](
[AttchID] [int] IDENTITY(1,1) NOT NULL,
[FileType] [varchar](30) NOT NULL,
[FileSize] [int] NULL,
[FileImage] [varbinary](max) NULL
)
I tried to extract a damaged document or pdf file in raw form and opened it with Hexinator software.
All the file that is corrupted, first its hex starts with the path of the file. In other words, it is manipulated.
Is there a way to retrieve data safely?
Best regards
|
|
|
|
|
lookilok wrote: But it doesn't work for some records
As stated this is not a database problem.
Potential causes. Could be others.
- You are not reading the entire contents completely. It works for some because the process you are using accidently reads if fully to the end or the lost parts are not actually needed.
- You succeed for some because you make assumptions about what it is or how it should be used. So nothing at all to do with the database. For example you assume that every one is a pdf when if fact some are word docs. There should be information in the database that tells you what the type is.
- The data is in fact corrupted in the database. So a data issue and absolutely no way you can fix it in code.
|
|
|
|
|
If we assume that this record is encrypted or compressed, Is there a way to show how the records with this problem were encrypted?
What encryption or compress method?
modified 6-Apr-23 3:57am.
|
|
|
|
|
Some encryptions and some compressions might put a signature at the beginning of the file.
If it exists it is not guaranteed to be unique but likely is sufficient to guess about it.
You would need to research each possibility. For example...
"Conventionally the first thing in a ZIP file is a ZIP entry, which can be identified easily by its local file header signature. However, this is not necessarily the case, as this is not required by the ZIP specification - most notably, a self-extracting archive will begin with an executable file header."
ZIP (file format) - Wikipedia[^]
|
|
|
|
|
As long as you are reading and writing the files in binary mode there should not be any problems.
|
|
|
|
|
This worked fine until I got to version 11, where the version numbers sorted like this - vers_id
0, 1, 10, 2, 3, 4, 5, 6, 7, 8, 9, I was expecting 0, 1, 2, 3, 4, ....
I'm thinking that I need another sort clause but sure how to integrate it into my case
But then I could be wrong, and my case statements may just need to be better.
Everything I tried failed. Looking for help on this to keep my customers project clean.
SELECT
Proj_Stage,
vers_id,
vers_note,
markup_price,
sell_price,
CONVERT(CHAR(19), sell_date, 120)<br />
FROM proj_vers
WHERE proj_id = '$projectNumber'
ORDER BY CASE
WHEN CONVERT(VARCHAR, Proj_Stage) = 'designing' THEN 0
WHEN CONVERT(VARCHAR, Proj_Stage) = 'engineering' THEN 1
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2
WHEN CONVERT(VARCHAR, Proj_Stage) = 'finished' THEN 3<br />
END
I tried this
END, vers_id
And I tried this
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2, vers_id DESC
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Just a stab in the dark, but you can Cast/Convert vers_id to a int. Cast(vers_id as int) in the order by should do what you want. Assuming vers_id is will always convert to an int.
varchars will be sorted in the way you are experiencing.
Jack of all trades, master of none, though often times better than master of one.
|
|
|
|
|
Great idea!
I just checked the table and vers_id was set to char.
Just crafted this and it does what I want now. I didn't think of that, where it sorted as chars and not numbers.
Good eye, or stab in the dark, very helpful and spot on.
SELECT
Proj_Stage,
CAST(vers_id AS int),
vers_note,
markup_price,
sell_price,
CONVERT(CHAR(19), sell_date, 120)<br />
FROM proj_vers
WHERE proj_id = '$projectNumber'
ORDER BY CASE
WHEN CONVERT(VARCHAR, Proj_Stage) = 'designing' THEN 0
WHEN CONVERT(VARCHAR, Proj_Stage) = 'engineering' THEN 1
WHEN CONVERT(VARCHAR, Proj_Stage) = 'construction' THEN 2
WHEN CONVERT(VARCHAR, Proj_Stage) = 'finished' THEN 3<br />
END, CAST(vers_id AS int) ASC
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Off topic - I would assume that "Proj_Stage" would be in another table and should have a sequence no in that table, this would eliminate the case statement in the where clause.
If it is input text then you have another problem altogether!
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'm stuck with the existing database to make it backwards capable, with older construction projects. Every record has a ...
project number
project stage
version number
This table keeps track of the versions of the construction project. As typical, customers that have construction done always change their mind during the construction process, and don't understand once you pull a permit from the city, you can't change the project. But they change it anyways and then you have to get a new permit.
I didn't get any documentation, nor useful comments and had to reverse engineer the old version to make this new version of the software. And it was very broken and needed fixing, thus below ...
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Even if you cannot change the existing tables in the database, you should be able to create new entities.
I'd still create another table with 2 columns in it as suggested by @Mycroft Holmes[^] as suggested above[^] (cols: Proj_Stage and SortSequence), indexed by Proj_Stage. Then
LEFT JOIN ProjStageSortSequence AS ps ON proj_vers.Proj_Stage = ps.Proj_Stage and change the ORDER BY to be
ORDER BY ps.SortSequence, vers_id . You would also need to change Proj_Stage in the SELECT to proj_vers.Proj_Stage. These changes will save loads of CONVERT() operations and should (not tested) run faster as SQL SERVER is very good at optimizing joins with low nos of rows.
This leaves the existing table unaltered and would be more efficient that the query it already has. It would not be as efficient or as normalised as using the sort sequence in the existing table; but it is a stepwise improvement.
|
|
|
|
|
Took a few minutes for me to absorb, but I get it now, and that's an enhancement that would be backwards compatible. I would be able to apply this new table to every other table that uses the project stages, which is pretty much about 15 more tables.
I'll dabble with this tonight at home in my new home office. Thanks!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Your Awesome! 
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Your welcome. Been there done that as they say.
Jack of all trades, master of none, though often times better than master of one.
|
|
|
|
|
Presumably no one is ever going to use version number 1.1 or even 1.0
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
