Category: noSQL

Let’s look at full text search indexing in PostgreSQL. Text search can be a common scenario for a lot of application developers / startups…Continue reading on Better Programming »

Herkese Merhabalar , Yep Yeni Bir NOSQL Veri Tabanı Serisine Başlıyoruz Bu Makalemde Neo4j Nedir Ne İçin Kullanılır ve Ubuntu Sistemi…Continue reading on Medium »

Note: the content of this article is dependent on my previous article…Continue reading on Medium »

Recently AWS announced a long waited support of MySQL 8 for their RDS Aurora. Of course, you could use MySQL 8 engine on RDS way before…Continue reading on Medium »

Continue reading on Medium »

Continue reading on Medium »

Continue reading on Medium »

A DBMS is a type of information system that stores and manages the data in a database table, for instance MySQL and PostgreSQL. The word…Continue reading on Medium »

Continue reading on Medium »

Continue reading on Medium »

Hello, I am not a software developer. I dont know any coding. I am only a computer user. I can use office tools fine. But I need a DB. I have an excel list. But I need more detailed statistics. I cannot add multiple record in single cell at excel for example. Such as: book name + author name1, author name2, page count this is one line in excel, when i want a detailed author statistics, excel count the author record as a single record. this is just an example. ​ So I want to transfer my excel list to a db. It should have a gui like microsoft access but I need a portable solution because in my office our computers does not have access. I want to run it in a stick. In other sub, someone recommended sqlite browserdb but I could not find a way to add multiple data in single cell and create a input form like access. ​ Any recommendations? submitted by /u/Industry-Regular [link] [comments]

PgAdmin 4's ERD Tool is good for the most part, although adding FK is kind of a pain. Too many clicks. I looked over docs and doesn't seem like there is a way to specify 1:1 to relationship. All FK default to 1: N. And also can't seem to change orientation of lines either. I have mixed feelings about the autoalign. Wish I could also box around some of the tables to categorize for easy scanning. ERD can easily get out of hand without boxing around. Was using draw.io, but the lag was very bad. Am I missing something in the docs? submitted by /u/cybernetics_engineer [link] [comments]

Hi, I am a mac user. I have set the postgres app on my mac, and I am looking for a full-feature including ER Diagrams, intuitive, M1 native, and not consuming much of RAM. I have narrowed it down to two apps: Postico and TablePlus (this one doesn't include er diagram), do you have something else in mind? Also, is Postico M1 native? submitted by /u/jiii95 [link] [comments]

SuperType Product id PK SubType Good product_id PKFK references Product.id weight SubType Service product_id PKFK references Product.id hours Are supertype/subtype relationships always 1:1, because the Subtype is an instance of the supertype? the subtype PK is unique so it must always be 1:1? But then if I think of this example, it makes sense to say a country can have many postal codes. Or is this not a true super/subtype relationship? SuperType Country id PK SubType Postal_Code country_id PKFK references Country.id SubType State country_id PKFK references Country.id submitted by /u/cybernetics_engineer [link] [comments]

I'm having a bit of communication breakdown with my instructor in trying to understand what actually defines "good logical design" in an ER model. We were asked to make a conceptual model of a fictional problem but everytime I rearrange it he'll suggest where he might move the entities but I'm struggling to get a direct answer for what design principal should be guiding us in our layout or entities and relationships. Do I care about good visual flow, or is it more about the logical flow of events from these entity relationships? He also mentioned needing a "start point." Is the starting entity where the entire process originates from? ​ Edit: Also, is it ok for relationship lines to cross over one another if nessecary? I was originally avoiding that assuming it was bad practice. submitted by /u/DiscountWHM [link] [comments]

Just starting my final year of university where I get to choose a project and one of the ones offered by staff is to build a data warehouse for my uni which seems interesting. I was wondering how hard this would be to do as I only have basic sql knowledge down but will have 7.5 months to work on this while also doing another module on big data & data mining? submitted by /u/RHSiuolF [link] [comments]

Here is Mark's blog post comparing of some of the data warehouses. He has recently reviewed DoubleCloud's managed ClickHouse offering: 1.1 Billion Taxi Rides in ClickHouse on DoubleCloud submitted by /u/ageje [link] [comments]

I used the free tier on Hasura (Hasura Cloud) where they spin up a free database on Heroku for me. I have my tables and columns setup and have tested it with real data and my website can read and write the data through the Hasura GraphQL endpoints correctly. Now I want to put my website in production and get users to sign up for real. What do I need to do with Hasura (and Heroku) to make the backend production ready? I figure I can easily upgrade to the paid tier with Hasura but do I need to do anything with Heroku? submitted by /u/lorenzomofo [link] [comments]

Im trying to figure out how to enter a customer that can sell products in my online shopping page. Should i made another entity called "seller" or should i add it as an atribute on the customer entity. Thanks in advance for any advice! ​ https://preview.redd.it/r0ssaes8lfp91.png?width=1248&format=png&auto=webp&s=34c00d3f0b85cc704b5a0635283f1d1e9a5df667 submitted by /u/Andmau00 [link] [comments]

Hi. I have been with my employer for about 7 months now. I started out as a network technician intern, just mapping stuff out and upgrading hardware etc. but they decided to keep me on permanently as IT support. It is a food manufacturing environment. This is one plant of several. I do keep rather busy with my current job, but my CFO (who I have really developed a great working relationship with) thinks that I can accomplish more and deserve more pay. He wants to reimburse me to either return to school or get some certifications in DBA (I do not hold a degree or certifications of any kind at the moment). He is VERY big on data and has always wanted someone internal to administrate our databases. He thinks that I could be the perfect fit if I obtain some education. I'm really excited about it I gotta say. Nervous and intimidated, but excited. So basically my CFO has left it up to me to do my own research on what education/certs I should go after, and he says that they will reimburse anything that directly benefits me growing into this new role. I was looking for some guidance on the best certifications (or even degree) that I should go after, seeing as how I have sort of been left to make up my own mind on it and I'm feeling a little overwhelmed. submitted by /u/throwaway90371560 [link] [comments]

In MySQL 5.7, the default isolation level is repeatable read. Let say I have a long running select query that takes 30 mins. to return, for example, select * from test_table where id < 1000000; Then when updates to these rows come, would the update wait for the select query to finish? I would want to test this but I don't know how hence why I am asking here. submitted by /u/cloyfan93 [link] [comments]

submitted by /u/Pancho1429 [link] [comments]

submitted by /u/Amit23456 [link] [comments]

I am stuck on trying to structure the tables for one portion of my database that has a lot of inter-dependencies.   I am trying to restructure a database at my job. We have physical folders with a number system for keeping track of our "jobs". The folder numbers are a four-digit year (folder year), followed by a dash, followed by a three-digit code (folder code). We do work in two different States, so the folder codes will end in even numbers for "State A" and odd numbers for "State B". However, this pattern alternates every year. In even-numbered years, folders for "State A" will have folder codes that end in even numbers, and in odd-numbered years, folders for "State A" will have folder codes that end in odd numbers. The reverse happens for "State B" (even year, odd code / odd year, even code). That is well and good. I have a table called "JobFolder" with fields: JobFolderID FolderYear FolderCode StateID (foreign key to a table containing States) I have a validation rule for the table itself that restricts records to having to follow the alternating even-odd behavior of the folder codes for the corresponding year and state.   Here is where I am racking my brain - there are three different types of folders.   The first type is what I'll call a "Standard Job Folder" (Std Folder), and it's pretty straightforward. The only pertinent information it requires is a Purchaser and a State. A single Purchaser can have many Std Folders in a given year. I already have a table with all Purchasers, so that identifying that would simply be a relation to that table.   The second type of folders are miscellaneous orders for a given year associated with a specific purchaser (I'll call it "Purchaser Misc." Folder). A purchaser might have an emergency order of something that isn't part of a specifically planned job (those get standard job folders). The pertinent information this requires is: a Purchaser a Folder year a State A Purchaser can only have one "Purchaser Misc." folder per year.   The third type of folders are what I'll call "Monthly Miscellaneous" Folders. These are like the miscellaneous of the miscellaneous. Purchasers who make much more frequent and large orders will usually get their own "Purchaser Misc." folder for the year, while Purchaser who make small, infrequent orders have those orders put in the monthly miscellaneous folder. The pertinent information this requires is: a month a folder year a State There are 24 "Monthly Misc." folders each year, 12 for each State.   I am struggling to design a table structure that can incorporate all of these interdependencies. My original idea has tables for each Folder type in addition to the JobFolder table, and relates to the JobFolders table with different composite keys consisting of two or three items, but that seems crazy. Any help would be greatly appreciated. submitted by /u/Doogie_Howitzer_WMD [link] [comments]

so i have this kind of ER scenario: https://ibb.co/ZXTZT0z I'm not very sure about the entity type of "Fund". So, i mean, is it a Weak Entity? Infact a Fund can exists only if there is 1 and only 1 Investor who transfer the Fund and 1 and only 1 Startup who raise the Fund. I don't understand if ID_Fund can be considered as a unique identifier for Fund (ie Fund is a Strong Entity) or if Fund is a weak entity of both Startup and Investor. Is it possible for a weak entity to have two or more strong entities? Or am I missing something with this concept? And what is the correct representation of my ER scenario? Please any help would be awesome! submitted by /u/parzival_bit [link] [comments]

I am currently back at full-stack engineering. I started with automated tests and then 9 years ago started backend development then after 2-3 years of it went to frontend. I had a pretty solid understanding of databases back then which I vaguely remember. Currently I need to write queries time-to-time and work some ORMs. Which made me curious about what has happened on the database front while I was pushing pixels in the browser. Things like playing nice with source control (for instance knowing what exact stored procedures are currently out in production just by looking at latest main branch) or modern language features (import/export modules, packages, etc.) or testing. During my time working I saw the following journey: Let's put validation and consistency as close to the data as possible When I started doing backend 9 years ago, we had a team of database engineers writing a ton of validation, parameter sanitization and consistency checks in forms of stored procedures, triggers et al. Since a lot of the actual business logic was in the database layer there was a constant ping-pong whenever a bug happened: Is this a backend bug? Or is this a DB bug? The database team also lagged behind in productivity essentially becoming a bottleneck. Not because they were bumbling simpletons, but because the language (TSQL) and the database itself was lacking luxuries like playing nice with source control, inability to do automated tests quickly and effectively, lack of CI/CD or modern language features like encapsulating, exporting and importing modules etc. Rise of business logic can only be on the backend This is of course an overly simplified story, but then I saw a clear direction to move as much of the business logic as possible to the backend. Since the language was different we could cover the logic with automated or unit tests. So this move had pros and cons: PROS * tests * developer experience (rich ecosystem of Java/JS/C#, etc. and of course) * source control (e.g. you now that latest of main branch equals code running in production) * CI/CD (checks run on pull requests, merging means deployment) All good, but this step introduced ORMs. And damned ORMs back then (and as I saw nowadays as well) did have problems like a simple query must be broken down into multiple fetch, transform and update calls. Or I was seeing people joining tables with JS functions, because the damn ORM did not allow them doing it in, or using SQL queries would mean a loss in developer experience (DX). And then you just pray that the other devs heard about transactions and concurrency, because if you change 1 update query into 3 separate DB calls the same record can change X times before your update finishes. From this I clearly see these cons: CONS * Kindergarten level SQL options: anything a tiny bit complicated is most likely not implemented or... * In a lot of cases encourages you to write code that is ineffective and/or dangerous compared to what any mid-level database folk would write, essentially you write logic/structure to play nice with the ORM in contrast to writing code for satisfying product, safety or efficiency principles. The way of Slonik I did stumble upon Slonik which was enlightening: you can write proper SQL queries inside javascript with the magic Tagged template literals. javascript await connection.query(sql` INSERT INTO foo (bar, baz, qux) SELECT * FROM ${sql.unnest( [ [1, 2, 3], [4, 5, 6] ], [ 'int4', 'int4', 'int4' ] )} `); This way you also get parameter binding which is a great solution against SQL injection. Uou get a lot of features of ORMs but I also can now use SQL what SQL is for with all its expressive power, now multiplied with all the goodies of a modern language like JS would offer. But the DB roundtrips problem still exist. 🤔 Which made me think: What if a slonik-like thing would be available on the database layer, as a composite of SQL + some modern language, a service/DB hybrid, expanding what can be written in regular SQL several factors, that has all these modern language features we like in C#/Java/Go/JS, but also does not need to do backend roundtrips? To reach this level I need to already make database round trips and the entire thing is optional: other services can just bypass my fancy slonik based backend layer and access DB directly. You see, let's say service A operates over database A, uses slonik and avoids a lot of the problems but service B operating over the same database A just uses string queries naively or a bit backward ORM and cannot keep the same level of security as service A. If we could have things like testing/mocking, modules, etc. on the database layer then it wouldn't be a pain to have some of the data related business logic in it, and all services could and would work with a ton of security and consistency, no need to replicate those business logic in several microservices (sometimes even using multiple languages). submitted by /u/dr_rodopszin [link] [comments]

Like Erwin but free? submitted by /u/Affectionate_Trick39 [link] [comments]

Hi! Im working with a table in psql with more than one billion rows, and i cant do selects because its take too much time or never ends. I created a index but the select stills take a lot of time. I was thinking, if i move all the data to mongoDb, the select query will be faster? because i need to get a lot of info from that table and it's really impossible. Any advice is welcome Thanks! ​ Edit: More information about my table. The hardware is a db.t3.medium of AWS RDS, with 1 core, 2 cpu virtual, and 4GB Ram. The most difficult query is like select count(distinct macaddress), router, floor, date, place from table where place = 1 group by date,floor,router,place order by date desc; . I made an index but (probably) is really bad designed. The table has 14 columns. submitted by /u/wxblex [link] [comments]

Hi all, This is my first time posting here - so if this is not allowed please delete it. I am currently studying Databases and am having trouble with the 3NF normalisation content. My course materials are utter garbage and don't help me at all. Is there a different way to understand 3NF normalisation? At the moment my understanding is essentially: If a column is reliant on another column that is not the primary key it is a transitive dependency and needs to be its own table; If a column is reliant on a column that is the primary key it is a functional dependency and stays in the same table as the primary key. With this being the case, I have been given a table (below) and told to 3NF normalise it. I have also put in my attempts underneath as well for what I think is correct. Is someone able to explain how I get to the 3NF normalised state of this table so that I am able to complete this task? Single underline is the primary key. Italicised is the foreign key. https://preview.redd.it/immg0c8i4wo91.jpg?width=1114&format=pjpg&auto=webp&s=bd8ab5b27c20eeec560677ac38ef535368342bd8 Thanks so much. submitted by /u/Tempest36 [link] [comments]

A product can have many parts. Parts can be used in many products. Product and Parts are Many to Many. A part can be stored in many units of measure. Ex: meters, tons, gallons, etc. Units of measure have types. Ex: Length, Volume, Mass, Area. part table will have attributes : inventory_level unit_type unit_id. product_parts junction table will have attributes: required_part_amount unit_type unit_id. unit_of_measure table will have attributes: id name unit_of_measure_type table will have attributes: id name unit_of_measure_conversion table will have attributes: uom_from_id (PKFK unit_of_measure) uom_to_id (PKFK unit_of_measure) calculation_step operator (restricted to +, -, *,/) factor constant product_parts's unit_type field will be a FK to part's unit_type. Which ensures they will have the same unit_type. I did not have unit_type as an attribute of unit_of _measure, but instead a whole separate table because a part can be stored in gallons, but the required amount can be quarts. The problem I have is now, is that the unit_id referenced in either part or product_parts can be mismatched. I don't know how to ensure that when unit_type of VOLUME in either part or product_parts restricts the unit_id to the right type. All types of units will be stored in unit_of_measure. Looks like I need to also store the unit_type as an FK on unit_of_measure? Even then, I am still stuck. Tried to make this as digestable as possible. submitted by /u/cybernetics_engineer [link] [comments]

Imagine you have a couple of reference tables... These tables are monthly updated and their values point to multiple fields across the DB. What's a standard tool used to manually change DB values in spreadsheets or any other user-friendly format? submitted by /u/rodrigonader [link] [comments]

So this is really the only place I can ask this and get an answer from people in the field. But I've always been told that people in the tech industry would never own iphones? Thoughts. And why? Lol. Also. Sorry if this doesn't follow guidelines. I'm just really curious! submitted by /u/Stock-Philosophy8675 [link] [comments]

I'm getting a bit lost deciding what should have its own table and what should be a field. I'm interested in the theory. submitted by /u/Affectionate_Trick39 [link] [comments]

submitted by /u/Amit23456 [link] [comments]

submitted by /u/cheeztoshobo [link] [comments]