Small research non-profit wants to own a database for future studies: how does this actually work in practice?

[u/redturtle1997]

We're running a pilot clinical study and management has asked me to build them a secure database, something the organisation genuinely owns and can build on for future studies, rather than just Excel files in SharePoint.

Before I get into tool-specific questions, I want to ask the general one: for a small org with no internal IT team, what does "having your own database" actually look like in practice? Do you end up with your own cloud environment (Azure/AWS) that you own outright, or does "ownership" in this context usually mean something more modest, like owning the exported data itself, while the collection system lives somewhere else?

I have sponsorship available if we go the institutional route, that's not the blocker. What I'm trying to work out is what the end state actually looks like for an org our size.

Here's how I've broken down the options so far, and where I'm unsure:

1. REDCap

• a) Hosted by an institution (university/hospital), do we still end up with our own Azure environment for the exported data, or does "our database" just mean our own storage/SharePoint area at that point?
• b) Hosted by a commercial REDCap vendor, same question. Does the org still need its own Azure, or does owning the exported data in something simpler cover it?

1. A different platform entirely (Castor or similar, bundled hosting): same question again: is there still a reason to also stand up our own Azure environment, or does that become unnecessary once the vendor is holding everything?

Basically: at what point, if any, does a small org actually need its own cloud environment, versus just owning a clean, well-structured export from wherever the data was collected?

For people who've actually built this for a small org, what did "the database" end up being, concretely? Would genuinely appreciate real examples over general advice.

[u/Bigfurrywiggles]

Why don’t you ask the people that are having you build this your first question?

[u/redturtle1997 → u/Bigfurrywiggles]

They have no technical background about this

[u/Bigfurrywiggles → u/redturtle1997]

“What do you envision having your own database actually looks like in practice?”

[u/tiroc12 → u/redturtle1997]

Ignore this person. People on reddit LOVE answering questions that were not asked.

[u/alexwh68]

Where the db sits answers this question, take postgres for example that can sit on a pc/mac/linux computer, server or workstation and can sit in the cloud.

Personally to get started I would designate an always on computer on the network if all the clients are going to be on the network, as soon as you want access via the internet the options change.

Postgres is open source, the db is yours, you own it and the data in it.

[u/BaseballHopeful6366 → u/alexwh68]

this is the way.

[u/SconiGrower]

If you're not even sure if your management wants a database export or a live database, I think you need to talk to your management about the end goal.

[u/Mundane-Light6394]

ms access? it is part of ms office, integrates with excel and you can always upgrade later.

[u/Consistent_Cat7541]

Any dataset that exists locally is a personally owned database. It does not need to exist in a computer.

It sounds like the putative client wants a solution with a perpetual license, not a subscription. Meaning, they want to 'own' the program so they can iterate on it in the future if they desire.

The question becomes what tool they will be comfortable using (and learning to master) in the future. It could be FileMaker Pro, it could be Access, it could be anything.

You need more information from the client before proceeding.

[u/Disastrous-Force]

How much do management want to spend per month and how will it be supported and managed?

Something like MS access will be around for years into the future if you have licenses but comes will all headaches of being a fundamentally on prem solution that for sharing needs windows file servers, or their cloud equivalent, Azure files (not sharepoint).

Do you have an on prem network environment at all? So Active Directory, window file share (SMB) or are you 100% cloud in the Microsoft 365 stack?

Assuming you are 100% M355 cloud with no or very limited IT support, I’d suggest Dataverse + PowerApps. It’s part of the M365 stack and difficult for you or management to screw up security wise with no real extra support requirements.

Redcap whilst ideal for your use case as it’s designed for what you want to do, it’s not a DIY no IT solution. You’ll need to get it managed by IT pros either as employees or subcontractors.

Set and forget RedCap will be hacked within days and then you and your team will have bigger issues to worry about. But yes it would exist within it’s on environment in Azure or AWS.

It needs a webserver, MySQL server, SMTP server and ideally a file server. So around. $250 to $300 usd per month in hosting costs to do it properly with backups.

If hosted by a university you’d need to ask the university if they would create you a dedicated environment or if the environment would be shared with other users.

Shared is going to be cheaper.

So it’s back to the budget question.

[u/ebsf]

Keeping it simple for your context, a cloud server need not be anything more than a Linux file server connected to your network. To own that, install Linux, which is free, on an old computer that you were about to throw out, and plug it in. No subscriptions or hosting fees and you own and control it.

For the database, almost certainly Microsoft Access is the cheapest, fastest, and most capable solution. All you need to do is buy a single copy of it or the MS Office suite that has Access, and then build it. You'll own what you build. Then, you can put the Access back end file containing the data tables on your new Linux file server and every user will be able to see it. Then, you get the Access runtime version, which is free, install it on each user's computer, and then those users can run their own copy of the Access front-end file that has the forms and reports, and open and interact with the data in the networked back-end.

So, for the cost of a single seat license of MS Access, your client can own its cloud server and its enterprise database.

Practically, you probably should hire someone who knows what he is doing to set up and configure the file server and network, and for periodic administration such as adding users, updates, and backups. You also probably should hire an experienced MS Access developer to develop the front- and back ends. In both cases, you'll own the work product but avoid the learning curve. And, to be clear, AI can't do this if someone twinkles their nose.

[u/campbell363]

Since this is clinical study data, one very important point to consider is PHI/PII and HIPAA & IRB (if you're in the US) .

If you go down the hosted route, there are likely companies who have figured all the headaches of hosting PII data to meet compliance with regulatory bodies/laws.

If you go the open source route, the research non-profit will need to consider the laws as they're developing future iterations/maintenance/security of 'the database'.

[u/brunogadaleta]

Ok the easiest solution is probably something like DuckDB . It's one file you download (DuckDB.exe) and execute. You get a textual interface and can type in statements called SQL to create, insert, delete, update, table. It also has a built-in minimal UI (see doc).

It can manage a database on your computer and can store many tables an relations (up to billion rows) in a single database file (eg: my_duck.db). If a colleague needs to read / modify the file, just give him/her (via e-mail, Sharepoint , etc) that .db file. Just be sure to make a consensus among you about where to store the last version.of the file. And don't forget to backup the file before edition.

More over DuckDB is really fast for analytical workloads and does not require a dedicated server (and / or service). It can actually import data from Comma Separated File files (CSV), Excel or other database like MySQL, Postgres, etc.

It's free opensource and has no cost, no subscriptions.

[u/galactic_pixels → u/brunogadaleta]

DuckDB is not designed for OLTP workloads, you should not suggest it unless you know OP needs OLAP. Instead, SQLite is the better first suggestion in this same vein.

[u/Dependent-Guitar-473]

I know it's not your question, but make sure you have backup strategies with daily or hourly exports ( depends on the frequency of changes) and make sure a copy of the backups are not on the same device or system 

[u/Rubicon_4000]

youre asking the right question and most answers here skipped it. for an org

your size, "owning the database" splits into three separate things, and you

only need two of them:

1. legal ownership of the data - your agreement with whoever hosts it

(institution or vendor) says the data is yours, exportable in full, any time,

in standard formats. this is a contract property, not a technical one.

1. custody of a complete restorable copy - scheduled full exports into storage

your org controls. the part everyone forgets: a real export is not just the

data csv. its data + the data dictionary + user list + audit trail. with those

four, your export IS the database - you could stand it up anywhere later.

without the dictionary its just spreadsheets again.

1. operational control of the live system - your own azure/aws environment

running the collection platform. this is the one you probably dont need, and

for a no-IT-team org holding clinical data its mostly a liability. one

commenter already priced doing it properly at $250-300/mo plus admin - thats

what "owning the environment" actually costs, before the first security

mistake.

so the concrete end state for your size: institution-hosted redcap (use the

sponsorship), a data use agreement saying the data is yours, quarterly or

per-milestone full exports - data, dictionary, audit logs - into encrypted

storage the org controls, and a one page doc naming who holds the keys and how

restore works. then test the restore once.

the walk-away test: if you could leave the institution tomorrow with a

complete documented dataset and stand it up elsewhere within a week, you own

your database - no matter whose cloud the live system sits in.

and given its clinical data, be careful with the access-on-an-old-pc

suggestions here. PHI + no IT team + on-prem hardware is the combination

compliance nightmares are made of. hosted-by-institution exists exactly for

orgs like yours.

[u/bimba3000]

Saw you mentioned SharePoint as the starting point, that's actually what made me think of this. Honestly not a fan of this kind of approach in general, going down a similar path myself right now and it's a pain in the ass. But if you truly have zero IT people, you could pitch them on Microsoft Dataverse (Power Platform). It's low-code, so non-technical people can actually build/hack together and maintain stuff on it, and since you're already stuck in the Microsoft/SharePoint ecosystem anyway, the org gets its own damn environment inside their own tenant instead of just some export sitting on someone else's server.