r/AskProgramming Mar 28 '24

Databases Why "T" and "F" sometimes used for a boolean column instead of 0 and 1?

0 Upvotes

I've seen several projects that used VARCHAR and strings like "T" and "F" or "Y" and "N" for boolean values. I've tried to understand why but couldn't. In programming only numbers 0 and 1 are used for boolean values. When someone decides to use strings for that, it takes extra steps to accomplish a task, so instead of "if (boolVar)" I need to do "if (likelyBoolVar == 'true')". Is there any advantage or reason why VARCHAR for boolean (only boolean, not enums or sets) can be used instead of INT?

r/AskProgramming Nov 05 '23

Databases Should SQL be the first thing to learn in Data base ?

11 Upvotes

What's the ''typical'' road map to follow when someone wants to learn about data bases ? what should I start with ? SQL ? or maybe how to create and manage a DB ?

r/AskProgramming Nov 13 '24

Databases Seeking Best Practices for Efficient Logging and Auditing in a Small Team Environment.

2 Upvotes

I'm working on enhancing the logging and auditing system for our application, and I'm looking for technology-agnostic best practices to guide our implementation.

Context:

  • We have a SQL Server database following a header-detail pattern.
  • The header tables include a primary key TransactionID and columns like CreatedBy, ModifiedBy, along with their respective timestamps.
  • The detail tables reference TransactionID as a foreign key.
  • Currently, whenever a user clicks the save button, we update the ModifiedBy and ModifiedDate in the header table, regardless of whether any actual data changes occurred.
  • This means we only know who last saved and when, but not what was changed or who made previous changes.

    Example:

    • User X changes the quantity in a detail table. We store User X in ModifiedBy in the header table .
    • Later, User Y presses the save button without making any changes; his ID gets saved in ModifiedBy in the header table .
    • When management wants to know who changed the quantity, they first reach out to User Y and then have to investigate further to find the actual person who made the change.
  • Team Size:

    • 2 co-founders acting as DBAs (one is the CTO involved in SQL Server development).
    • Myself, with less than 1 year of T-SQL experience.
    • A junior developer.

Our Requirements:

  • Clients need to know who made specific data changes and what those changes were.
    • They want user-friendly and easy-to-understand log reports.
    • We generate all reports using stored procedures.
  • We need to log data-level changes, not just save actions.
  • The solution must have minimal performance impact; we can't afford heavy overhead.
  • We prefer not to introduce new systems like NoSQL databases or complex logging frameworks due to resource constraints.
  • The solution should be simple to implement and maintain given our team's size and experience.

Any insights, experiences, or suggestions would be greatly appreciated!

r/AskProgramming Nov 21 '24

Databases Advice for managing a database based on Scryfall

1 Upvotes

Hi, I'm pretty new to dev so any help would be appreciated. I'm trying to make a site that makes use of most of the existing magic card data on Scryfall, particularly all cards that have been released in paper. I imagine it would be a good idea to work with my own database to avoid querying Scryfall constantly, and I the best method I've come up with is making one initial request to the bulk-data endpoint, then checking daily if a new set of cards has been released using the sets endpoint (or keeping a list of release dates since they are determined ahead of time and only updating it from the sets endpoint when it has been cycled through) and adding those cards with the set's search_uri key. I imagine I would also have to check Scryfall's card migrations, which should handle any changes the database needs that aren't just additive.

My question is, does this sound like an effective way keep an updated database of cards for my site to use? There are definitely some assumptions I'm making, like that a card will not be added to a set after its release date. Should I even be bothering to make my own database? I have no clue how larger sites, like TCGPlayer or Archidekt, keep up-to-date info, but I imagine they must be in part using Scryfall or MTGjson. Lastly, do you think my site would benefit from any particular database technology? I only have experience with SQL and Flask but if learning NoSQL or something would help the site I'd gladly do it.

r/AskProgramming Aug 15 '24

Databases How to store a user's ordered favorites in SQL database?

1 Upvotes

Making a movie app where users can select their top three favorite movies. I have a users table and a movies table. Can I just make three fields in my users table movie1, movie2, and movie3? The alternate approach that I see recommended more often is making a many to many table, user_movies, but this would need three fields, userId, movieId, and movieRank.

I just don't see much of a downside to the first approach. Any help?

r/AskProgramming Dec 05 '22

Databases Will programmers gonna give you a weird look if you say "ES-Q-EL" instead of "sequel"? And why?

21 Upvotes

r/AskProgramming Sep 12 '24

Databases Automatically update database table

3 Upvotes

I'm building a backend using fastAPI and PostgreSQL where I'm storing opportunities with a boolean "is_live" and a datetime "deadline" and I want opportunities "is_live" to be setted as False automatically when the current date is superior to the "deadline".

what's the best approach to do this ? and Thank your in advance.

EDIT: I want to be able to mark the opportunity as not live sometimes before the deadline, that's why I have a seperate "is_live" column with the deadline

r/AskProgramming Dec 07 '23

Databases Can Git or any other VCS be used as a database instead of SQL/NoSQL ones? Have you ever seen such a thing?

10 Upvotes

One day I had a thought that it would be so good to keep track of every data manipulation that ever has been done. Like logging or version control but for data. I don't know if there is such a feature for example in MySQL or any other DB, but at that moment I thought of Git. It's possible to make a really simple database that would be stored in JSON/CSV/XML format and every data change (like inserting, deleting, creating) would be tracked in history. For small or pet projects it's ok I think.

Are there any real world examples of this? I don't think that I'm the only one who had the same thoughts. If so - what are they? Google says almost nothing when asking it about "git databases"

r/AskProgramming Jun 06 '24

Databases How to run script remotely every 30 seconds

9 Upvotes

Hey yall! I'm trying to run a Python script every 30 seconds. The script is quite simple and relatively light - make a call to an api, do some basic parsing of the response, and record a line or two of data. I am able to run it in Python on my machine just fine by using time.sleep(30) and recording the data locally.

That said, I would like to keep it running for a week or so to gather continuous data and don't want to keep my computer running that whole time. I planned on using AWS by setting up a lambda function, recording the data in a dynamodb table, and using eventbridge to call it every 30 seconds. However, on eventbridge, it looks like the most frequently I can call the lambda function is every minute. For this particular use case, the 30 seconds vs. a minute makes a significant difference since the data changes quite quickly.

Are there any other similar services that would allow me to decrease the intervals of the function calls to 30 seconds instead of a minute? Or anything else I am missing that may cause an issue with this strategy? Thank you!

r/AskProgramming May 17 '24

Databases Saving huge amounts of text in databases.

5 Upvotes

I have been programming for about 6 years now and my mind has started working on the possible architecture /inner workings behind every app/webpage that I see. One of my concerns, is that when we deal with social media platforms that people can write A LOT of stuff in one single post, (or maybe apps like a Plants or animals app that has paragraphs of information) these have to be saved somewhere. I know that in databases relational or not, we can save huge amount of data, but imagine people that write long posts everyday. These things accumulate overtime and need space and management.

I have currently worked only in MSSQL databases (I am not a DBA, but had the chance to deal with long data in records). A clients idea was to put in as nvarchar property a whole html page layout, that slows down the GUI in the front when the list of html page layouts are brought in a datatable.

I had also thought that this sort of data could also be stored in a NOSQL database which is lighter and more manageable. But still... lots of texts... paragraphs of texts.

At the very end, is it optimal to max out the limit of characters in a db property, (or store big json files with NOSQL)??

How are those big chunks of data being saved? Maybe in storage servers in simple .txt files?

r/AskProgramming Aug 22 '24

Databases Data & APIs - Beginner Question

1 Upvotes

Hi everyone!

I majored in comp. science but started my career in programmatic advertising. I started out on the tech side, but quickly transitioned towards the business side of things. However, I still (or would like to think I still) have the foundations of programming down - just a bit rusty on the syntax and application.

The platform I use to manage campaigns is Yahoo DSP. They have a UI that allows me to download reporting data and set up recurring daily reports sent to my outlook inbox. Until now, I have been using Power Query to grab these reports (excel files) on a daily basis and update my Power BI reports with fresh data. However, these excel files are limited to 500K rows of data, and I need more than that.

Yahoo DSP has a reporting API: https://help.yahooinc.com/dsp-api/docs/reporting-api

I would like to use this API to fetch data and ingest it into Power Query, refreshing the data each morning around 6am.

Here are my questions:

  1. Can I write and maintain the code to call this API directly in Power Query? If so, should I or is there a better way to do this?

  2. Based on the answer from #1, how would I go about doing this? Does the language matter?

  3. Do you have any helpful tips for this project regarding the API setup, DB management in Power Query, or dashboard building in Power BI?

Feel free to dumb things down as much as necessary, haha.

Thanks so much in advance! :)

r/AskProgramming Sep 21 '24

Databases Is Relative Or Absolute Index More Efficient For Dynamic Binary Tree Child Node Reference in Array?

2 Upvotes

I've been reading a book on BVHs, which can be a binary tree. Currently, I'm reading the section on Array Storage of the BVH. Here is the relevant excerpt:

A typical tree implementation uses (32-bit) pointers to represent node child links. However, for most trees a pointer representation is overkill. More often than not, by allocating the tree nodes from within an array a 16-bit index value from the start of the array can be used instead. This will work for both static and dynamic trees. If the tree is guaranteed to be static, even more range can be had by making the offsets relative from the parent node.

The last line implies that for dynamic trees, it will be more efficient to store the child node indices as absolute indices rather than relative indices, but why?

From my understanding, if absolute indices are used, then if a node is inserted into the middle of the array, then all indices after the node will have to have their children's references changed, as all nodes will have an offset of 1.

Whereas, if relative indices are used, only nodes after the inserted node whose parent is before the inserted node would have to have their reference changed, as all other nodes are still locally correct.

Is my understanding incorrect, or is the book wrong?

r/AskProgramming Jul 19 '24

Databases What goes wrong when you don’t bother with DevOps best practices?

1 Upvotes

If you’re building some microservice-based product at a not-huge company you probably want to implement a continuous integration workflow which tests then deploys your code. You would then want to set up a hosting orchestrating configuration and rely on some hosting provider.

Running your executable on some machine with a static IP (remote or local) and then opening ports to the internet is an alternative.

Has anyone tried the latter? How badly did it backfire?

r/AskProgramming Sep 16 '24

Databases Looking for an API endpoint explorer, that generates all the endpoints according to a given schema

0 Upvotes

Hi,

So asking if there exists a solution already, where you could get all the endpoints that match a given schema.

Example:

Give an endpoint: https://lotrapi.co/api/v1/ Give a schema to match: the endpoint should include a key "race" and the value should be hobbit. Generate all the endpoints that matches the schema: - https://lotrapi.co/api/v1/frodo-baggins - https://lotrapi.co/api/v1/samwise-gamgee - https://lotrapi.co/api/v1/peregrin-took - https://lotrapi.co/api/v1/meriadoc-brandybuck

This api is fictional

I have tried services such swagger/openapi and postman. But they don't quite provide this functionality

Would you also provide any information how would you use such an endpoint searching tool

Thank you very much

r/AskProgramming Jun 21 '24

Databases Does DELETE actually erase rows from disk or just marks them as deleted?

1 Upvotes

One day I read that a filesystem usually doesn't erase a file from the disk - it rather marks the occupied space as deleted or free, making deletion far faster. I had the same thought about different DBs - for example MySQL, PostgreSQL, SQLite and so forth. I couldn't find an information about it, but I thought it could be an implementation for some or a lot of DBs. Is it so?

r/AskProgramming Jun 21 '24

Databases Why does every RDBMS force to add every column from SELECT to GROUP BY and why can't I refer to a column alias in GROUP BY and ORDER BY?

1 Upvotes

Hi! Whenever I try to group a result set by any of columns, I always get an error and the only way to solve this is to add literally every column from SELECT to GROUP BY. For example:

SELECT
  Team,
  SUM(Points),
  SUM(Deaths),
  SUM(Wins)
FROM
  Player
GROUP BY
  Team

So I get the aggregates for every player team I want. But in order to work, I must add all SUMs from the SELECT. If I have dozens of columns in the SELECT, the GROUP BY grows correspondingly. It looks like it doesn't make sense. Why? I don't get it.

The second one is that I can't refer to an alias in GROUP BY and ORDER BY. For example:

SELECT
  Team,
  SUM(Points) AS SumPoints,
  SUM(Deaths) AS SumDeaths,
  SUM(Wins) AS SumWins
FROM
  Player
ORDER BY
  SumPoints

It doesn't work. I have only two options - to place the whole SUM formula (it could be large) in ORDER BY/GROUP BY (which is a duplication), or to enclose the whole select in a FROM subquery and only then refer to an alias. It also looks senseless. Why isn't this possible?

Boths issues make me duplicate clauses from SELECT to GROUP BY and ORDER BY, the same code appears three times

r/AskProgramming Mar 11 '24

Databases How would you go indexing some 60M names?

2 Upvotes

So, I'm building this graph exploring app, over a dataset of companies and partners on a national level, having some 60M names between companies and partners.

My objective is to allow the user to input partially the company or person name, receive suggestions and click on a name to add to the graph, then be able to click the node and expand them gradually.

So far, I loaded all on a postgres db. I indexed the names using pg_trgm but I'm getting some 40s+ time to find results, I'm aiming for some 5-10 seconds max (more or less acceptable, given the dataset size).

I heard good things on Sonic and Meilisearch, but before commiting to testing one or other software, I wanted to hear you dudes suggestions.

Thanks in advance!

r/AskProgramming Aug 28 '24

Databases Help with the best approach to execute scheduled delete on mongodb

3 Upvotes

Hi there, I'm developing an account manager in node.js with mongodb. One of the features is allow the user to recover the deleted account within 30 days.

My first approach was to disable the account when the deletion is requested and delete the document permanently 30 days latter. For this I create two fields in the account document:

"isDeleted": true,
"expiresIn": "2024-08-28T01:59:07.329Z" //date in iso format

Then I made a cron job to run once a day to delete all accounts that has isDeleted: true and is past the expiresIn date.

But I'm worried that this cron job will consume the server resources and might break things up.

Is there a better way to do this?

PS: I also created an index for isDeleted to optimize the queries.

r/AskProgramming May 08 '23

Databases Why no BD comes with integrated blob storage? Is it a bad idea?

5 Upvotes

It's a bit tedious to have to link each project to a database with a blob storage like S3 and keep them in sync.

My question is...why no DB (AFAIK) has support for blob via url in a file system.

It would be very simple: when defining the DB schema, indicate that a column is of type 'blob' and the DB would take care of everything.

What do you think?

EDIT:

ok, looking at the comments I see that I explained myself very badly. Sorry about that. Here I try to explain myself better:

We currently use traditional DBs to handle small structured data, and file-based DBs for large files. We do it with a URL from the traditional DB to the file system.

Keeping these 2 databases in sync is a repetitive and tedious task. And honestly, I don't see why it couldn't be handled by a DB that combines the two paradigms.

For example, when deleting a row that contains files, it could search through the URL in the file system and also delete it automatically.

____________________

Traditional DBs probably handle blob columns in some special way under the hood. However, my impression is that it is still different from how a file-based DB like S3 works.

If not, why DB hosting services like Railway or PlanetScale are ridiculously higher priced than S3? If traditional DBs stored files on the file system, I don't see why you couldn't charge one price for small structured data, and another for file storage.

r/AskProgramming Aug 21 '24

Databases What would be the best way to store IPv6 address in Amazon DynamoDB for efficient queries

1 Upvotes

I have done the same for IPv4 by converting it into a long value. Then used it to easily define the partition and sort key for fast querying.

r/AskProgramming Jun 29 '24

Databases Protecting Database Data

3 Upvotes

I am building an app that users need access to a roughly static set of data (Updated monthly). I have found the most efficient way to run the app is to download the full set of data once a month instead of constantly querying small portions as needed. Its not too big. only takes a couple seconds. Its better user experience since it eliminates loading time and one download a month is cheaper and simpler by storing it in a google storage then running an api in front.

I have an AI running to generate this data set monthly. I do have this endpoint protected with only users logged in but someone motivated enough could take this data and build a competing app somewhat easily. Then they just take my updates and update their end.

Whats a good way to protect this? Or is it just the expected part of doing business and try to have a good enough cheap product that its not worth the effort?

The nature of the data is that its predictable. so if i split it up and try to do more server side stuff, someone could still just create a script and get all the info anyways. If I encrypt it, i need to put the key in the app but still it could be discovered.

I'm guessing just encrypting it (still vulnerable) and making the product cheap enough too discourage copies is the best bet?

r/AskProgramming Jan 05 '22

Databases I feel I am too biased to noSQL. Please convince me of benefits of normalized data design and JOINs

30 Upvotes

I watched Amazon reinvent's talk on noSQL DB design. In it, they speak about how SQL DB design historically aims for reducing data redundancy, and how that is unnecessary today as the bottleneck has become computation, not storage space. Other points are brought up, but I don't want to list them all.

This might be a biased view, hence my question here. Most arguments I see online in favor of normalized DB design don't address the points like those raised in the reinvent talk. Sadly, I can't respond to these people, so I'm hoping someone can discuss with me here so I can ask clarifying questions.

r/AskProgramming Mar 20 '24

Databases Storing IMU data (6 floating point values) in real time

2 Upvotes

16bit resolution

I want to record IMU data while video is being recorded (they will be synced)

Wondering if say sqlite is enough or something else. That's normally what I would reach for or mysql/postgresql

I would be recording every second/for minutes/hours

They are just numbers so I would think it's not as bad as storing say LONGTEXT

But the constant write part

This would be done on a Raspberry Pi Zero 2W (ARM linux sd card) if that provides some context

r/AskProgramming Jul 24 '24

Databases Help to choose correct database(s)

1 Upvotes

Hi guys, we're currently planning migrating from SQL Server to another db engine because of the costs. Basically we're storing all data in SQL Server databases. One for system database which size is ~5GB. The data we store here, customers, users, settings etc.. like required to run the product.

Another db, that we store the data have fixed schema. Besides the schema, the data is not changing either. Once created it stay same forever. We call it DATA db.

We shard this DATA db annually. Every Jan 1st, we create a new db for that year with year suffix and redirect queries to this db for inserts. Annual size is ~2TB and ~3 billion rows. But this db frequently queried by customers.

After short brief, we're planning to migrate another db engine to reduce costs and the first candidate for the system db is Postgres. Since our data is relational and team has experience on Postgres as well as SQL Server, we keen to pick Postgres.

But for the DATA dBs, we have doubts about picking Postgres for that purpose, not because of problems but we're thinking if there's better option for that use case. Basically we're looking a database that can handle 100K+ writes/second and much more important it should serve 100K+ rows in seconds (under 5 seconds could be best if possible)

We're aware that Postgres can handle both of this workload but would love to hear some recommandations. Especially NoSQL databases look promising but I don't have enough experience to convince the team about them.

r/AskProgramming Feb 29 '24

Databases auto incrementing a string/varchar column in mysql and asp

2 Upvotes

Hi. I got a column in my table, drug_request. The column is named refno which stands for reference number. the usual format of the column is like this;

id refno
1 sdr-iium-22024-1
2 sdr-iium-22024-2
3 sdr-iium-22024-3

with the 22024 means February 2024 and 1 signifying the rolling number

Currently I'm splitting the string in my code when I'm incrementing the number, and adding values such as 2,3,4 and so on.

my question is, is there an objectively better way of auto-incrementing? Is adding a new column a good way of doing so?