I’m trying to store daily snapshot of Employee data for FTE analysis project – analytics on how many FTE in various positions any given day. I can call a REST API, which will give me data for all the active and terminated employees as of API call time. Is it prudent to call this API every single d…
Tag: database-design
How to decompose the tables for optimal querying
Suppose I have three tables Actor, Movie, Producer with following attributes Actor(Id,ActorName,Bio) Producer(Id,ProducerName,Bio) Movie(Id,Name,Description,ActorName,ProducerName) There can be many actors in a movie and actor can act many movies, same goes with producer. How to effectively decompose/create n…
SQL – Get the MAX COUNT access peak having only entrance and exit date
I have a table that keeps the access to our system, and it have two columns called entranceTime and exitTime Eg: What im trying to get is the “peak” of simultaneous connections, i tried using a subquery with MAX and COUNT like this: But with this query im getting the count of 5 instad of 3, is it …
designing a follower/following schema between two tables in SQL?
suppose we have two tables: Brands Users i want to design a system where: Brands can follow Brands Brands can follow Users Users can follow Brands Users can follow Users this is what i have done so far: ed_id: user_id of the followed er_id: user_id of the follower note: the two tables are different but i have…
How to solve deadlock when inserting rows in sql many-to-many relationship with minimum cardinality one restriction?
This year I’ve been learning about relational databases and how to design them. In order to strenghten my knowledge, I’m trying to design and implement a database using Python and sqlite3. The database is about a textile company, and, among other thigs, they want to keep information about the foll…
When to create a subtype or simply use optional attributes
I’ve been practicing with Oracle SQL Developer for some time using school assignments and encountered some kind of dilemma while doing the Entity Relationship Diagram for one of those, here’s my issue: On the assignment they mention that there’s a CLIENT entity with certain attributes, and i…
Email address lookup in SQL Server
We have a large subscriber database and have a lookup based on email address of type nvarchar(100). It sometimes times out probably because of a large scan of it. I want to improve this, but weary of some of the options. Would an index on it be ok, I can handle the additional storage if needed, or a computed …
How appropriate is one column table
Does this kind of design come along with overhead or data redundancy? The structure of tables should remain able to do CRUD on tag, for something like manga/anime tag, allowing specific resources found-able through selection of tags. * representing primary key. Answer There is nothing at all wrong with your d…
1:N relationship to profile or user table?
Suppose I have a User Table and a UserProfile Table that I have separated. The two tables have a 1:1 relationship. User table contains only data like email and password, while UserProfile contains …
Store hourly data efficient way
There is a requirement to store hourly data in SQL Server 2016 and retrieve. It’s an OLTP database. I will explain with an example: we need to capture temperature of each city of a country and store on hourly basis. What would be the best and efficient design to do this. The data would be stored for a y…