#databasedesign
Not every query needs a primary key.
Keyless Entity Types in EF Core let you query database views, raw SQL results, and reports without requiring a unique identifier. Perfect for read-only scenarios and complex data models.
https://woodruff.dev/keyless-entity-types-in-ef-core-query-data-without-primary-keys/
Many-to-Many relationships in EF Core is just easier! No more manual join tables—EF Core handles it all!
Learn how to set up, query, and navigate many-to-many relationships without causing any trouble.
Read more: https://woodruff.dev/many-to-many-made-easy-mastering-relationships-in-ef-core/
I've been working on this a bit lately, but getting stuck on the database side of the IPAM relationships. Specifically, how to get a one-to-many relationship working between the network (e.g. 192.168.1.0/24) and the IP entries (192.168.1.[1..254]). Pydantic usually complains about the relationship in some form or other, saying it can't generate the schema.
One thing that definitely doesn't help is that I define the return types in one file and the Pydantic models in another, so any change I make in one I have to make in another.
For that reason, I might look at replacing how I interface with the database.
Thankfully, the testing I have in place will let me change things out easily.
Gateway stuff is done for now, so it's time to work on the DHCP range data.
A network can have 0..n DHCP ranges and each DHCP range should be editable with the CRUD operations.
C - create: add a new DHCP range, make sure it doesn't overlap with another DHCP range already present on this network. All IPs should be in range, too.
R - read: render DHCP range information and overall IPAM utilization of the network. A DHCP range covering 200 IPs on a /24 should, for example, show roughly 80% of the IPs are used. Vuetify has UI elements to render stuff like this.
U - update: change data about the range such as the start/end addresses, name, or description.
D - delete: remove a DHCP range <--> network association and the associated record(s) from the db.
Validations:
DHCP range doesn't overlap other IPs in use on the network, such as the gateway, static IP assignments on that network (which I haven't even gotten to), or other DHCP ranges.
There's going to be a LOT of testing to go into this and I think that's why I like this kind of stuff. It forces me to think of all the use cases, write tests, add code, and balance all of it together.
Also; if your previous technology choices made this hard... That means a wider discussion to fix it, and don’t let outside people pressure you into compromising on data intregity in systems that need auditing. The bad ones will throw your reputation away anyway if they pressure you to do bad work and it messes up later #databasedesign #DatabaseDevelopers #systemDesign #Accountancy #Audit
The nuance that seems to come up is parent records with child records - ie if you remove the association, the associated child audit entries disappear.
Its easier to see the issue if you remove this type of record keeping to other situations- ie if a foster parent looks after a child, say the kid breaks a leg playing football while under the care of the carer, then the kid moves on…. You’d want to know that both the kid had the accident with the carer and that the carer looked after a kid that had an accident while with them. Both sets of information either end are needed. The accident shouldn’t disappear from the carer’s record.
Yet you hear this from system designers or developers.
I’m starting to think that anyone who designs or builds systems that handle data should do a basic bookkeeping course that covers how double entry works.
The accountants managed to work out for us since the 13th Century that if you move money from A to B, both A and B need an audit entry describing the movement each way. Write only - nothing is removed. Yet I don’t see an equivalent teaching in the various system design books, despite this concept being fundamental to any system that needs (financial and non-financial) auditing capabilities.
Recently during an interview the question was about how to improve query execution and how to avoid a single query to crash an entire node.
Half into it they told me that they can't spend 10 years building their databases and the solution I got was not that complete or smart. Reasonable but then you get to this paper 
Luckily
https://muratbuffalo.blogspot.com/2024/10/auto-wlm-machine-learning-enhanced.html?m=1
Join us for a deep dive into MySQL database design and modeling with Alkin Tezuysal. From denormalization to scaling techniques, we'll cover it all. Room 106, Friday, March 15, 2024, 14:30-15:30. Don't miss out! #MySQL #DatabaseDesign #scale21x https://www.socallinuxexpo.org/scale/21x/presentations/design-and-modeling-mysql
Using a UUID7 as the primary key of a table (as opposed to a UUID4) has many benefits (as noted here: https://uuid7.com). But if the key is meant to be customer-facing, isn't there concern about the world knowing exactly when the corresponding entry was created (by extracting the timestamp from the UUID7)? What could an unethical attacker do with that information or am I worried about nothing?
Any example scenario exposing when a UUID7 would be a security concern would be greatly appreciated.
#database #uuid #databasedesign
Dive deep into the world of #DataResidency strategies with this insightful #InfoQ article: https://bit.ly/482LuxB
Learn effective implementation tips while ensuring a positive experience for all stakeholders.
Dive into the world of music collecting with my post on building a multi-tenant music collectors database! Perfect for devs and music enthusiasts alike. #MusicTech #DatabaseDesign 
Today, I'm specifically working on some #CloudFormation to turn this database into a reality.
Next up with be an API, then interface! 
https://compositecode.blog/2023/10/23/building-a-multi-tenant-music-collectors-database/
Bloody idiots.
Database design 101:
DO NOT IMPOSE ARBITRARY LIMITS ON THE LENGTH OF PEOPLE'S NAMES!
I'm filling out a form, and it asks for my first name and last name. It says that it must match my driver's license... except that it limits the first name to 12 characters.
My name is like
George-Alexandre Desjardins
(Not my real name.) So my first name is more than 12 characters. My driver's license does not truncate my name.
I entered as much as I could and let them sort it out.
Karen Lopez 10 Physical Data Modeling Blunders
#Database #DataModeling #DatabaseDesign
https://www.slideshare.net/KarenLopez51/karen-lopez-10-physical-blunders
The Key to Keys - Database Design
A primer on Key terminologies and uses.
#Database #DatabaseDesign #DataQuality
https://www.slideshare.net/KarenLopez51/the-key-to-keys-database-design
Hi Friends,
Looking for a new #freelance #software #development #project. Would 
to #getfedihired.
* >20 years coding #Python, #C#, #Java, #JS, & #TS.
* Vast exp. building #DistributedSystems in the 
(#AWS, #Azure, #GoogleCloud) & on-prem.
* Authored & teaching academic #course on #DatabaseDesign + #workshops on #TestAutomation, #k8s, the #ActorModel, #CleanCode...
* #Empathy is my super-power, I love to help, teach, build and fix.
Please RT - Every BOOST helps 
My name is Rafael. I was born in #PuertoRico and I've been living in #NYC for the past four years (I moved to #Brooklyn two years ago.) I'm 47 years old and I like:
#Computing
#ComputerProgramming
#SoftwareDevelopment
#AI
#VisualArt
#Design
#Film
#Literature
#OOD, #OOP
#GeneticAlgorithms
#DotNet
#ASP
#CSharp
#Java
#HTML
#JavaScript
#SQL
#DevOps
#DatabaseDesign
#FullStack
#BusinessToConsumerApps
#TechnicalWriting
#UbuntuLinux
#Brooklyn
#Manhattan
#NYC
#PuertoRico
#UniversityOfSussex
#Brighton, #England
#VideoGames
#ContentModeration
#LongWalks
#Music (Who doesn't?)
My personal website is at: https://human-assisted.blogspot.com/
https://c.im/ (I'm mod here.)