New answers tagged database-design
0
Just to give you some ideas here are some of the ways I have seen this implemented:
all interactions with the database use stored procedures and views to limit access
groups are implemented in Active Directory. Members are added to groups. When a user logs on and is authenticated to the domain by AD the application calls a stored procedure which returns all ...
1
You try to insert Albums.ArtistId value (7) which is absent in Artisis.ArtistId values list now. So insertion fails.
You cannot insert album of <unknown>, FK claims this is impossible, and server prevents this.
Insert artist with ArtistId=7 first, then insert his albums.
2
There is no "better" between those two designs. They are just different. So it depends on what you want to store in your database. If every field you store belongs to just one object, then the first design is the better choice, but if you want to have fields and objects stored and each one can relate to another, the second design is better. Let me give you ...
1
When you decompose R(T) to find the BCNF, if the dependency X -> Y violates the BCNF, the decomposition that the classical algorithm requires is not R1(X) and R2(T-X), but R1(X+) and R2(T-(X+ - X)).
So the first decomposition, considering AB → C, should be:
R1(A, B, C, E) (since AB+ = ABCE)
R2(A, B, D) (= {ABCDE - (ABCE - AB))
In R1 the remaining ...
0
Depends if a different admin or a different user could join in on the followup conversation, other than those who started it in the originating Complaint.
If a complaint's message thread is forever tied to the single, originating user, then both the connection (foreign key) and idUser column should be excised from ComplaintMessages. Same with the admin if ...
0
Use additional table for roles:
CREATE TABLE ROLES (ID INT, NAME VARCHAR(30), PRIMARY KEY(ID));
INSERT INTO ROLES (ID, NAME) VALUES(1, 'Publisher');
INSERT INTO ROLES (ID, NAME) VALUES(2, 'Editor');
INSERT INTO ROLES (ID, NAME) VALUES(3, 'Viewer');
Then your PERMISSIONS table could look as follows:
CREATE TABLE PERMISSIONS (
USER_ID INT NOT NULL,
...
1
you should have a table for the brands, another one for the models related to their respective brand, another one for the submodels related to the submodels and in the last table use compound keys with the keys of the tables and the year and create views for queries.
0
You can use one table and such as 'car' and have a field to store the part name as door, wheel, etc
car(user_Id, part_Id, part_name, position, color, price, car_model, car_submodel, car_year)
postion = side, front/rear
or
you can create another table to store the ids
car(car_Id, car_model, car_submodel, car_year)
and use the car_Id in other tables
1
Your table is going to be very narrow with two INTs and a DATE, so even with hundreds of millions of rows, storage will be relatively light.
You can also create summary tables off of this main table, like if you need monthly or yearly logins, you can create these as separate tables.
0
You can try with monthly partition table design using date column. It will keep each month's data in appropriate data files and easy to manage. Index B-Tree structure would faster in this method with big volume of data.
Index searching page count will be less and faster as per B-Tree structure
Easy to make read-only, remove specific files from database
Can ...
1
In MongoDB 3.6 and newer, shards must be deployed as a replica set. Replication provides data redundancy and high availability for each shard; sharding enables a collection to be distributed across multiple shards.
For more information see Sharded Cluster Components and the Deploy
a Sharded Cluster tutorial in the MongoDB documentation.
1
If you want to hold the positions as text that's fine. But there are better text editors than Postgres. Holding each game as a file and each position as a row within that file, in FEN notation, would be a good approach. The files can be read and searched using the tools of your choice (grep; Python). Expanding the spaces as Daniel Vérité suggests would make ...
1
Your post actually has multiple questions.
How do I model this?
How do I enforce this?
By themselve, each one would be a very good question for this site.
But, the complexity of the answers (taken as a whole) may generate a 3rd question.
How do I design a program for this?
How do I model this?
There are 4 different ways to model "related widgets"
broad ...
0
From comments:
This question is incredibly open-ended and has an entire branch of the industry devoted to it, namely data-architects and program-architects. The best thing to do is to pick ONE concept and stick with it. Thousands of users is a low-threshold for performance, so any solution will work. Are you expected to present a design or an ...
1
Here is one approach I use this model. I suggest being careful with attributes, do not turn everything into an attribute, I have dealt with DB models that use such an approach several times. It makes what should be simple query a mess, and means tracking types, and lots of casting to and from data types.
treat the below a psuedo code just to get the ...
1
Can I do this against a live production table, or do I need to bring down the database?
Yes. You can do it against a live table. Will even be very fast since citext and varchar are binary coercible, so no table rewrite is required (since Postgres 9.1).
But this still acquires an ACCESS EXCLUSIVE lock on the table, which makes any concurrent access on ...
1
You ask two different questions here:
[W]hen should I use a clustered version for a graph database?
This one is easy: as soon as your scalability or availability requirements cannot be met by One Giant Server.
[G]raph databases work best in a single server configuration[...] What is the reason for this statement?
Looking at the full book quote you ...
0
For the simplicity of maintaining it in the future (especially if it's not you) I would go with your first design. It contains a table with information on the user (login, name,etc), a table for the site (location, category) and then a table linking a person to a site (Steve is the account admin at Site 3) The second design would make things more complicated ...
0
I would suggest that you do something like this (see fiddle here).
Create an author table and populate it:
CREATE TABLE author
(
author_id SERIAL NOT NULL PRIMARY KEY,
author_name TEXT NOT NULL,
author_name_no SMALLINT NOT NULL,
CONSTRAINT author_name_name_no_uq UNIQUE (author_id, author_name_no)
);
INSERT INTO author (author_name, author_name_no) ...
0
Move the deleted rows to a new table, that is the same structure as the current table. Use a UNION if you want to select both deleted and non-deleted items
1
I take this table structure to mean the records are never actually deleted just marked as deleted so these are are excluded from the searches. This is not the best design to use datestamp field for this
consider
For a composite index the size of the index will be big plus overhead
UUID is 128 bits + 32bits for timestamp = 160
ID is int 32 bits + 32 ...
3
A retrieval by primary key using where id = ? is pretty much constant in time regardless of the size of the table. Adding a condition with AND won't change that (using OR would be a different thing though)
If you need the same performance for queries using the condition on the uuid column, create a (unique) index on that as well.
If you always query with =...
0
From comments:
mongoS doesn't need much memory/CPU (or even its own machine) and can be located (installed) on the config servers.
config servers don't need that much memory, even when mongoS is there; 1-2GB is enough.
– JJussi
2
Another option is to use simple table inheritance for your events, with the event as the base type:
CarEvents RentalEvents
========= ============
ID <------------------ EventID
EventType | CustomerID
CarID | RentalDetail03
EventDate | ...
MilageReading | RentalDetailNN
...
1
A quick solution might be to make a trigger in the Work_Area table that checks the Interests table to see if that field already exists, and if it does not then insert it as a new record. This would prevent the need to manually add an author's new interests, just add their new article and let the interests table take care of itself
0
This is my method. Flatten the data by doing an integer divide, then multiply to get into 5 minute segments
select dateadd(minute, datediff(minute, '1900-01-01', p.[date])/5*5, 0), SUM(p.rate)
from payments p
group by dateadd(minute, datediff(minute, '1900-01-01', p.[date])/5*5, 0)
order by 1
0
Adding my comment as an answer since it solved the problem:
Try IS NULL instead of = NULL -- I forget that myself sometimes!
I think someone that knows about mathematics would say the NULL is nothing so an equals comparison does not work as expected.
2
[MAX] columns are to be considered like BLOBs (Binary Large Objects) and are physically stored on another "page".
This means that practically, they are like on another table, seamlessly linked to the one with the rest of the data.
One of the main limitations is that you can't index the [MAX] column.
I don't see any issue in you doing things as you suggest.
0
Normalization is good in an operational database, and bad in an analytic database. Normalization helps prevent update anomalies, but makes analysis harder. Generally speaking, for analysis, you want a dimensional model, which is flatter and allows for duplication
2
You can use either a computed column or a view:
create table rooms (
id int,
title varchar(100),
single_bed_count int,
king_bed_count int,
bed_count int generated always as (single_bed_count + king_bed_count)
);
insert into rooms (id, title, single_bed_count, king_bed_count)
values
(1, 'room 1', 1, 2),
(2, 'room 2', 2, 2);
select * ...
0
This probably should have been a simple comment, but as I am a new user I'm not allowed to post those. I'll try to flesh out the answer a bit.
You statement is correct for the requirements you've set: your single table would be sufficient. However, you seem to be modeling with the questions you already know will be asked in mind. You are ignoring the ...
0
There must be a rule for value range from value, which implies you’re losing data: what if the bands are revised later?
Better to store the detail (sale cost) & model the banding separately.
Day has already been mentioned (wouldn’t be 3nf with both, see your texts)
I would also model location and represent here with an FK rather than include it.
...
1
One point you might consider is to store the actual sales value any way. If you store ranges a user must convert from value to range and might make errors. If you create a table storing the ranges, the system can do this task. You will also be able to changes ranges later.
Normalisation also means not storing redundant information. As you can derive the ...
2
I had the pleasure of maintaining a one-man application that used enums for everything, and I can say as a young DBA it was a nightmare. I had to contact the app developer a dozen times just to get the values for his enums to build my own lookup tables. The best part was when the customer wanted an additional option for one of them, and he just inserted the ...
1
I've always preferred portability over "tricks". While it's true that nowadays most RDBMS support enums, it has not always been the case. As for the cost of joining with these "status" tables, often the RDBMS optimizer is intelligent enough to keep them in memory as they are "always" accessed so performance is not an issue here.
1
I find your design a bit backword, can a delivery_note_partner_A exist without a corresponding delivery_note?
Assuming that is not the case, I would suggest:
CREATE TABLE delivery_note
( delivery_note_id ... NOT NULL PRIMARY KEY
, ...
);
Depending on how much processing of the notes for each partner you are going to do, you may consider something like:
...
0
In this case, Party is a superclass. Person and Organization are subclasses.
The superclass/subclass situation comes up all the time in the real world.
Unfortunately the relational model doesn't contain inheritance, which is the best way to model this.
Your two answers are variations on one design pattern, called Class Table Inheritance.
In this pattern, ...
0
Keep your referential integrity. Option #3 is your best choice here. However, instead of inserting a null value into LanguageCodeValue, create a new record with a key of -1 and value "Unknown". Any records inserting into Sense_LanguageSource with a null LanguageCode foreign key should use -1 as the key instead. Thus, any joins will show the LanguageCode ...
0
Possible realization:
id SERIAL PRIMARY KEY,
punter_id INT,
bet_number INT,
guess_number TINYINT, -- from 1 to 13
guess TINYINT, -- from 0 (or 1) to 7
UNIQUE (punter_id, bet_number, guess_number)
Each bet consists from 13 records for each punter.
The result is placed into the table with similar structure:
id SERIAL PRIMARY KEY,
bet_number INT,
...
5
The best, most definitive, and most correct way to do this is to not mix your "test" and production data in the same database in the first place.
Testing against production is going to result in a major, serious event for the business at some point. For that matter, the number of people who even have access to that production database should be minimal, for ...
0
Using phpMyAdmin run a SQL query against the table that you want a column of data from and select only that column (something along the lines of SELECT cas_number FROM analytes).
Scroll to the bottom of the results and click on Export. The only thing that will be exported is the column of data that was selected.
Top 50 recent answers are included
