New answers tagged database-design
0
Double Quote are indicators for column, but postgres escapes single quotes by doubling it
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON tags FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(
tsv, 'pg_catalog.english', 'translations#>>''{en,name}'''
);
1
You can used use the WINDOW Function DENSE_RANK for getting the a new Column, to get an order for the post .
You need a outer Select as window functions can't be used in WHERE directly.
CREATE TABLE posts
(
"title" varchar(30),
"author" varchar(30),
"created_at" date
);
INSERT INTO posts VALUES
('Johns first post', '...
1
You can use ROW_NUMBER window function to set an order, then simply use it as a subquery with a proper predicate.
SELECT FROM
(
Subquery
)
WHERE
post_num <= 2;
SELECT
title,
author,
created_at,
ROW_NUMBER() OVER (PARTITION BY author ORDER BY created_at) as post_num
FROM
posts
title | author | created_at | post_num
:...
0
I think what you are looking for is CUBE datatype to store 128 dimension number. My suggestion would be to use PostgreSQL.
PostgreSQL supports CUBE datatype and native support for Euclidean distance computation.
a <-> b Euclidean distance between a and b.
To support 128 dimension, you have to install PostgreSQL from source as binary installation ...
1
To add to the other answers about normalization, outside of theory on how a database should be structured to represent the data, there can be practical considerations that might make splitting a table sensible.
One possible implementation of ACID you would find in Postgres, for example, involves deleting and reinserting a row on update and later reclaiming ...
1
Reducing the number of tables isn’t a goal, either in design theory or in practice. Reducing tables can help or hurt performance, which is why in practice people increase or reduce tables (regardless of duplicate data).
Roughly speaking, increasing the number of tables is helpful when you have data that is rarely used in conjunction, decreasing the number ...
0
Like nbk mentioned, it doesn't really matter much between the two. The only thing I'll say about the first is it's a little denormalized and can be logically confusing to store the address_id in two places, the Users tale and the User_addresses table. I'd personally opt for the second design and not worry much about making two updates, since an extra update ...
0
I’m strongly of the opinion that it should be on the user table as default shipping address with a foreign key to the address table. That way you have made the database ensure that the address can not be deleted which could be done if you had implemented it with a flag on the address table.
0
it doesn't matter.
As you have to get the message from the second table, you always have to inner join it ,adn add a where clause, to get the right one.
So the only thing would be size
A boolean needs 1 byte
Your default_user_address_id would be bigint which needs 8 Bytes, so as most peoplem have only 1 address or maybe two, you would save space with ...
0
My guess would be the fallout comes from how you're running your tests. I believe it is possible to run into heavily favored test parameters for one set of your data vs the other after inspecting your example test query. Specifically your WHERE clause:
WHERE date >= '2021-02-06'
AND date <= '2021-02-07'
AND site IN ('c3b3771c-4b48-41a9-88eb-...
4
The “Normal Forms” are narrowly defined in terms of eliminating redundant data and “update anomalies”. Whether fixing other schema design problems counts as “Normalization” can be debated, but in general parlance Normalization just means ensuring the database complies with some Normal Form.
1
As a new PostgresQL user, I searched the internet for answers regarding the same question you're asking. I read so many posts and so many different articles from so many different people. Still none of them gave me a satisfying answer. I was about to give up when finally I found someone who gave me the answer I was looking for.
The right question to ask here ...
0
Your proposed database design sounds reasonable, what isn't working about it that makes you question otherwise? A NoSQL solution isn't the right one when you have a defined schema and need to ask more complex questions of that schema such as data transformations on your relations and aggregate functions such as your example shows.
You theoretically could ...
1
I'm not entirely sure what problem you're trying to solve, but if you grant the kind of access you're describing to "Everyone" and you get hacked, now you have opened up a lot more possibilities for someone to do damage to your systems. That is access that they could not have done if access had only been granted to a select few people. It sounds ...
0
This is a classic 1:n-relationship between the users (1) and the content (n), for which Django has exellent support and documentation: Django docs on 1:n-relationships.
0
I can't comment so I'll post here.
If I understood you correctly, you wish to know which posts each user created?
If so.. create another table "user_posts" which will have a foreign key with a user_id and then all your post data, Then if you want to find which posts are associated with each user you can use a JOIN.
0
As Colin touched on, it's hard to follow what schema problem you're trying to solve with just generalities. Unfortunately the devil's in the details with this kind of thing, since one detail could drastically change the best approach.
Looking at your database diagrams at a high level the tables A, B, and C in the first diagram look like the same entity in ...
0
One reason for normalization is to smartly decouple a table into multiple tables of related columns so only the needed set of columns are queried at a given time to maximize performance. If the CompanyId is a standard in most of your queries, then from a performance perspective it's not a bad idea to keep it in all relevant tables so you don't have to join ...
0
You have Exercises and Muscle_Groups. The relation between them is:
One Exercise works one to many Muscle_Groups
One Muscle_Group used by zero-to-many Exercises
Therefore there is a logical association (many to many) between them which we would implement as a junction table:
CREATE TABLE Exercise
(
ExerciseName VARCHAR(20) NOT NULL
,CONSTRAINT ...
0
This kind of dimension is called Role-Playing Dimension.
This is tipical of Multi-Dimensional SSAS modeling.
However SSAS Tabular modeling does not support role-playing dimensions; you can overcome that limitation by using DAX, enabling inactive relationships for specific calculations (you can define multiple relationships between the same tables in Tabular)...
0
You have normalization errors. If the allowed values are:
| preference_id | allowed_preference_value_id | description |
|-----------------------------------------------------------|
| 1 | 1 | Red |
| 1 | 2 | Blue |
| 1 | 3 | Green ...
2
What will be the relation between Employees and Players, given that some players are also Employees?
can you give an example of exactly why there's a N:N with unique constraints for both Emp_id and player_is?
CREATE TABLE employee (employee_id INT PRIMARY KEY, ...);
CREATE TABLE player (player_id INT PRIMARY KEY, ...);
CREATE TABLE junction (employee_id INT NOT NULL,
UNIQUE (employee_id),
FOREIGN KEY (...
1
LOBs are managed in chunks (groups of blocks), not blocks, so there's not really anything to gain with this. Oracle assumes that an 8k block size is "universally good" for everything. By increasing the block size, you could potentially wind up wasting storage space that can't be efficiently allocated for smaller files because the chunk size is too ...
1
I see at least 2 possibilities.
First - BEFORE INSERT / UPDATE triggers which checks the amount of enrolls per student and forbids insertion / updation if the restriction fails. UPDATE trigger needed for to restrict errorneous data while updating with student ID change.
Second - add enroll_number column which is NOT NULL, ENUM('1','2','3','4') and UNIQUE in ...
1
Your main issue is you're using an INNER JOIN between Machines and Orders which means only return the rows that match (in other words, only the rows where Orders exist). Rather you should use an OUTER JOIN, in this case a LEFT OUTER JOIN (which can simply be written as LEFT JOIN) so you get all the rows of Models and Machines, and the ones that don't have ...
0
what do you want to fix exacly?
Performace?
or is the boudary the issue?
DELETE FROM Model
WHERE ID NOT IN
(
SELECT Model.ID
FROM Model AS mo
INNER JOIN Machines AS ma
ON mo.ID = ma.ModelNo
INNER JOIN Orders AS O
ON ma.SerialNo = O.SerialNo
WHERE O.OrderDate >= '2015-01-01 00:00:00'
GROUP BY mo.ID
);
0
To add onto what mustaccio said, DRY Principle makes sense in the context of the data itself which may drive how you architect your database, and from the database perspective actually has an official name known as normalization. But most importantly is your schema models the business requirements without going overboard with over-normalization.
If you ...
0
Put the COALESCE in a view write queries against the view, not the base tables. It would help if you could remove access to these tables and grant access to the view only.
There will be a small runtime penalty with this, as two tables must be read. If you could change the load to perform the COALESCE once at load time that would be better. Subsequent changes ...
1
So you're not too far off, you're just missing a couple key things, the first being the JOIN keyword. JOIN is how you can combine two related datasets. There are different types of joins, one being an INNER JOIN which is how you can combine two datasets and only return the rows that match the join condition. For example, if you wanted to get only Customers ...
1
So it's actually pretty easy to normalize when there's multiple fields, some with varying amounts of data points in a single field of the same row. Just follow this rule: Any column that has multiple data points within the column of the same row should become it's own table. So in your example that could be Cast and Genre. It's immediately apparent that ...
1
Normalisation is to remove information from tables, that are repe4ated by many times and ids as int are smaller than any text.
The bridge Tables you need, because you have a m:n relationship between film and users(cast, director, musician...)
Occupation is in my opionion a attributs of the relationship between film and user
Film (idfilm,Titel, plot,Wiki_Page,...
-2
It prevent Aoplicatuions from inserting invalid data. P.For example for many currecncies it maks no senes to have more than 2 digits right from the decimal point.
0
Why not just get rid of the main_user and sub_user tables and have a nullable field on the users table called parent_user_id. Then you can use a self-join on users.parent_user_id = users.user_id to get all the sub-users for a given Travel Agency. (When the parent_user_id field is null, that's a Travel Agency.)
0
This might sound a bit crazy, but as noted by ypercube in the answer linked in the comments, it is best to consider this a separate relation: for which we need a new table Question_CorrectAnswer, and it's PK is QuestionID.
This means that we only need to update a single row to change the correct answer.
Our only issue is that to prevent update anomalies, ...
5
There is no single right answer to a vague question like this, and the interviewer isn't expecting one (if they know anything themselves of course). They are looking to test how you realise the question is too vague and how you might start trying to answer such a question in a real situation - to see if you understand what extra details would be needed and ...
11
These kind of questions are stupid interview questions in my opinion, and definitely need more context. Perhaps they just want to see your thinking and what follow up questions you'd ask for clarity.
If by processed they mean 650 MB of data is added every month (and even here would be an assumption that the added 650 MB is data + all indexes + any other ...
13
This is an open-ended interview question that doesn't have a precise answer. The interviewer might expect you to ask additional questions for clarity.
Actual space requirements will depend on what is meant by "processed" (inserted?), data retention (are data ever deleted?), column data types, indexes, storage overhead, use of compression features (...
-1
As you seem to prefer a 1:1 relationship between questions and answers and you also don't want to reuse answers, add the CorrectAnswer to the answer, so you can have multiple correct answers per question and a re so more flexible.
CorrectAnswer is more a Attribute for answers than for questions, so it makes more sense to put it there
Question ...
0
If you add CorrectAnswerId to your question table, you don't have to update all the answer rows, assuming only 1 question is correct.
That being said, you probably may want to have multiple choice questions, right? Is not a big deal to have to update multiple rows in a transaction.
Your design will normalized enough with either option.
0
Your approach is unnecessarily complicated. Your situation boils down to this:
You have users (also persons), with their corresponding attributes, including login credentials and such, but also status and last login date.
A user (person) can have one (or more?) role(s).
A role has one or more permissions.
A user can be created by one other user, so the user'...
1
Honestly it really just depends on your use cases, but for me I would likely just store all 10 columns in the same table and call it a day (unless they were very unrelated to each other, then I might normalize it a bit). Having the full list of fields might influence different answers, and your approach is a valid one as well. It just might be more work to ...
0
If you attached the ER Diagrams you made for the other modules it would be easier for us to advise on how to relate them. But you basically should have tables that create a relationship across models, and that's how you would combine them into a final single ER Diagram.
For example, the Class Room Management module will likely have it's own table of Subjects ...
0
What you are basically looking for is a reverse engineering tool for Postgress. For the database products I worked with, the maker provided a reverse engineering tool that could extract the metadata stored in the database itself and transform that back into a database create script, written in SQL. I don't know Postgress, but I expect there to be such a ...
1
As nbk mentions, the middle table is known as a bridge table or linking table. JSON is best for structureless or variable structure situations, but not so good when needed to be used in predicates within the context of a relational system.
By stuffing a bunch of values in a single JSON field and then using the IN clause, you're telling MySQL to treat the ...
0
I don't really understand what you mean by "looks a bit clunky and could be broken down better in a few more tables"; something looking "clunky", however that applies to database design, doesn't seem like a good reason to breaking things up. Unlike fighting the dark force, you probably should use logic, not your feelings.
For a suggestion,...
0
There are times where someone wants to write code and not specify each individual column. If I write a script to split out one customers data to another database, not specifying the column names means that I don't need to change the script every time I add a new column. As long as the column order is constant. If there is one table that you want to change ...
3
Anything that can exist on its own, without reference to anything else, needs to have its own table, so that gives you two to start with:
select * from Sets ;
+----+--------------------------------+---------+
| id | name | lego_id |
+----+--------------------------------+---------+
| 1 | Spider-Man's Doc Ock Ambush | 6873 |...
2
... does anyone know official sources where the threats of a public database connection is described or why it should not be done?
How about the Open Web Application Security Project (OWASP)?
Take a look at their Database Security Cheatsheet
Also, try this "Accepted Answer", over on Software Engineering.
0
I think your best approach would be to start with just tracking the counters in sys.dm_db_index_usage_stats. Just store the results off to a table (and have that run on a schedule) for as long as you feel necessary. Then look at the counters to see if there is significant movement on them. There will always be a little, especially if you have maintenance ...
1
Yes, multiple deadlocks can occur on the same resources, depending on the complexity of the race condition occuring.
Top 50 recent answers are included
