New answers tagged database-design
2
You'd have to add the crows feet (or whatever notation you prefer) for us to properly understand the diagram. I choose to interpret it as monotonic top-to-bottom.
top table
| |
^ ^
Left Right
| |
^ ^
Bottom
Fig: Diamond pattern
As such I don't believe it is a circular reference. For that you'd have to be ...
0
No, the name of a student doesn't determine his or her identity and hence the student number.
There can be more than one person with the same name, but they still would have a different student number.
This is the reason why databases usually have artificial numeric identifiers like that, and the reason why you have customer IDs, social security numbers ...
1
Indexes use space, and a 16 byte key is nothing to worry about.
So you should define the primary key on user_relations on (relating_user_id, related_user_id). If you need to search by relation_type, it won't help to put the column into the INCLUDE list, since such columns cannot be used as filter for an index scan.
I see two options:
In addition to the ...
2
There are a couple of scenarios where I'd consider replacing a "real" value with a surrogate & look-up.
First and most common is when I want to control the possible values in a column. I can define a table to hold the acceptable list, give it a surrogate key and reference that key in my main table. This is the classic foreign key situation.
Sometimes ...
2
OK, I won't rant about what is 'wrong' in your schema. I will provide an efficient way to batch the normalization for a batch insert.
I will point out two common flaws when "over-normalizing":
Don't do it if the id is bigger, on average, than the data. Note: INT is 4 bytes; BIGINT is 8.
Don't normalize "continuous" values -- anything numeric or date-...
0
Use lower case in Postgres. Postgres folds identifiers to lowercase, unless you double-quote your identifiers. To make Postgres operations easier, use lower_snake. If you need to bind to an API, etc, you can create a view with “CamelCase” aliases. For example, we do this with data stored in Postgres that is pulled by Domo.
1
With my original design, this view is simple to implement and it performs well. But if I get rid of the column TableB.price, how would I implement this view
create table TableA (
id int,
v_time date,
price int,
special_price int
);
create table TableB (
table_a_id int,
v_date date
);
create table SpecialDate (
v_date date,
description varchar(5)
);
...
2
You need a function which extracts the IP addresses into an array. The regexp you gave doesn't work for me, so I came up with my own crude one, you will probably want to tweak it to suit yourself:
create or replace function extract_ip(text) returns text[] immutable language SQL as $$
select array_agg(x[1]) from regexp_matches($1,'\d+.\d+.\d+.\d+','g')f(x)...
2
In the real world, a lot will depend on how many customers you have, how paranoid those customers are/ how valuable the data is, and how skewed your customers are (i.e. Mom & Pop with 10 items and 100 sales a day vs Hostess Inc with millions of sales a day).
Separate databases create a variety of administrative problems. Every time you want to do an ...
0
Both approaches seem wrong. You shouldn't have the name of an attribute stored in the table. Rather, that should be the name of the column.
Different kinds of items should be stored in different tables if they have different objects and properties. If they are quite similar, it might be better to use a single table - a couple of NULL values won't harm.
...
3
C is a non-prime attribute (not part of the candidate key), and C depends on (A,B), which is a proper subset of the candidate key.
So the table is not in second normal form by definition.
Since BCNF implies 3NF and 3NF implies 2NF, this implies that the table is not in BCNF.
0
In general, there are different canonical covers of a set of functional dependencies, and a canonical cover is called minimal if it has less dependencies of any equivalent cover.
So, for instance, in your example the cover:
A → B
B → C
C → A
is minimal, because it has 3 dependencies and it is not possible to find a cover with less dependencies.
There are ...
1
The missing index code is extremely simple in what recommendations in suggests. One thing is that it doesn't care about selectivity.
The reason to include columns is to cover a query. I.e., so you don't have to do a bookmark lookup for each row. If you have a rather high selectivity, then these lookups don't matter much, and it is extreme overkill to ...
1
Your approach is the one I would use.
You forgot a unique constraint:
ALTER TABLE equipment ADD UNIQUE(id, type);
That will be the target of the foreign key constraints.
I would not store type as a string. Rather, I would use a lookup table for the types and store the numeric identifier. This will prevent typos, and it will reduce the required storage ...
0
The simplest set up is probably something like this:
Table A
-----------------------------
| *id | time | price |
-----------------------------
Table B
------------------------------------------------------------
| *id | table_a_id(f) | start_date | end_date | price |
------------------------------------------------------------
Which you ...
1
Is there any way to maintain a constraint like this at the database level?
Yes and No.
Yes This is done via ASSERTION
No ASSERTIONS are not (currently) supported by any of the major RDBMS. Some minor DBs claim to support them.
Workarounds There are various methods to hack "what is currently available" into something that enforces such a constraint.
...
1
Another level of indirection to the rescue!
Add a Week table, containing the date of the first day of the week and whatever else seems sensible (week number, Year, etc), with one row per week. The Paycheck table has an FK pointing to the Week table.
Add a Day table. This has one row for each day, and contains the Date and an FK to the relevant Week table. ...
1
A concern that exists is that a song may be released as a single, then as part of an album, then re-released in a "greatest hits" album. This would lead me to have a song table, then create a join table that would connect the album table to the song table. This join table would allow for a single song to be connected to a number of albums. The PK of that ...
0
not sure this help but in my case have table with 3mil row. very simple join or union or loop queries all very slow.. for only 20 row results affter test and test I just put a simple command to query
FORCE INDEX (one_colum_indexed)
which one_colum_indexed you has to test one by one if your table have multi index field, to look for which one best.
all ...
0
The other answers make valid points & I've up-voted them. I'd like to address some of your other concerns.
"I understand that there are some rules .. but .. this just seems too much".
The point of normalization is to remove update anomalies. Ideally if one fact in the real world changes then one column of one row in one table must change. If the schema ...
0
You say this isn't the real database, so we're reduced to guessing about what the real problem may be like. I understand you may not want to give away proprietary information, but couldn't you anonymize the real problem? Anyway, for the sake of discussion, I'll discuss this as if the problem you give is the real problem.
The first database design makes ...
1
Is this an application that was developed in house? Or was it a packaged application that the company purchased (and potentially customized)? I'd generally expect that a "database for an eshop" would be purchased rather than being built internally but that's far from a guarantee. If this is a packaged application, this is quite normal.
Generally, ...
0
I see other issues with this particular database structure, but to answer the OP question directly,
No, using lookup tables such as School and ClubType does not violate any normalization rules. This is quite a standard practice to build tables around id values and have easily readable text values in a lookup table.
To address the issues I see in the ...
1
From my point of view your student table is "strange" ... most schools won't limit students to 1 club.
I would make the student table as "short" as possible
** Student **
Student Id, Name
Even your column school Id is questionable because you'd usually have the entire database for ONE school - so school Id is oblivious. But that may come from your try to ...
0
The second design does not support multiple clubs of the same type in the same school, depending on the DB application that may even be two clubs who exist in different points in time (in our school the students journal ceased to exist an was restarted).
Connected to this there are a multitude of things which you can not represent or which will be awkward:
...
19
By changing to your proposed solution you lose information from the database. The existing solution says what clubs can exist in a particular school irrespective of anyone actually being in that club at any point in time. The proposed solution requires someone to join the club before the club comes into existence (i.e. before a row is written to the database)...
4
does the first example violate some known rule of database normalization or some other mathematical principle? Or is it just a case of poor design?
Neither. It has no obvious defects of either normalization or good design.
It sensibly models the propositions like the following:
There's a school named 'School1'.
There's a ClubType named 'Spanish Club'.
...
0
Plan for growth by storing the lyrics in a normalised way using a separate table.
The advantages would be:
You can add more langauges easily should you expand to new languages in the future
If MySQL is set to create one table per file you can manage the storage of your lyric table differently to the others (e.g. the song table could live on some small ...
0
As per normalization, you should not have similar value attributes in multiple columns. In future, let's say, if you have to add 10 more languages later, your table will become huge. Moreover, adding new columns in a table with lots of data in a production environment is not so easy. Relational data should not be considered like a Flat Excel file. Searching ...
1
You have 6 many-to-many tables linking one table to 6 other tables??
If any of them is just one-to-many, toss the linking table.
Normalization is good; over-normalization is bad.
Why have a table LOCATION if the only thing it contains is a column LOCATION?
Many-to-many tables are somewhat inefficient. (See this for a partial solution: http://mysql.rjweb....
1
Here is a functionally equivalent (and IMO easier to read) version of mendosi's code for MariaDB/MySQL, with some additional exposition and slightly simplified logic in some areas.
Create a Numbers table if you don't already have one:
CREATE TABLE Numbers (number INT UNSIGNED PRIMARY KEY);
DELIMITER //
CREATE PROCEDURE populateNumbers()
BEGIN
SET @x =...
0
I was thinking about getting rid of the project_id field on batches, but we need to keep the reference.
Don't do that. It's useful keep the "grandparent" key in the foreign key. That way you can get all the batches for a Project without JOINing the intermediate Order table.
eg:
create table Project
(
ProjectID int not null primary key
)
create table ...
2
How many of the tables are not connected to any other table by a FOREIGN KEY relationship? You can do this by checking out the answer here.
It is possible to have tables with 0 records - if you ran a nuclear power plant for example, you'd want the table catastropic_failure to have 0 records.
Some reference tables could only have 1 or 2 reference codes - ...
1
For such a structural change in your data model you will have to go “all in”. You can’t model the same entities in 2 ways. As you correctly observed, it will lead to endless grief for years.
That means your “old” view of the world will have to adjust.
Have you considered creating a single order to contain all batches for existing projects, and name it “...
0
I suggest this data model:
CREATE TABLE entity (
entity_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE
);
CREATE TABLE item (
item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE
);
CREATE TABLE inventory (
entity_id bigint NOT NULL REFERENCES entity (entity_id),
item_id bigint ...
1
You can enforce the column nullability based on the template_id by using CHECK CONSTRAINTS, for example:
ALTER TABLE [item] ADD CONSTRAINT CK_Template_Columns
CHECK (
(template_id = 1 AND usp_1 IS NOT NULL AND icon_1_asset_id IS NOT NULL AND icon_2_asset_id IS NOT NULL AND heading IS NULL AND usp_2 IS NULL)
OR (template_id = 2 AND heading IS NOT ...
31
First: There is no distinction, collation-wise, between biblical Hebrew and modern Hebrew. We are just dealing with Hebrew.
Second: Regardless of anything else, you want to use the newest set of collations, which are the _100_ series as they have newer / more complete sort weights and linguistic rules than the older series with no version number in the name ...
8
It depends on a lot of things. Collation is sorting, comparing, and non-unicode code page.
This repo has a good list of options around Hebrew.
+---------------------------+---------------------------------------------------------------------------------------------------------------------+
| Hebrew_BIN | Hebrew, binary sort ...
1
It would seem you have cascading deletes on and you'll end up deleting someone who could theoretically be referenced in another group as a treasurer or secretary.
The group table is violating the 1st normal form. This is akin to having address1, address2 in a table.
You need a third table GroupPersonRole that holds groupid, the person id and the roleid.
...
0
I would not think that credits and time would really have anything to do with each other. One is essentially a count of how many credits they have accumulated, and the other relates to their status as a student. As far as the degree program and start date, those are also just data points to keep in the student's record.
For more info on this topic, this ...
0
As others have stated the future possibility of different fields and different uses means you probably don't want them in the same table.
However depending on the use of your database I will mention a way I've seen companies combine Sales and Purchases:
An Inventory Transaction table that stores any activity that impacted inventory. These tables combine ...
1
Atomic in this sense is a logical concept. Would you consider the integer 123 not atomic because it has more than one digit?
123 is an agreed upon singular fact, representing a point on the numbers axis.
A movie title is the same - it represents a singular agreed upon fact, that a movie with that title exists in the real world.
A non atomic value would be “...
0
It doesn't matter much if you use an uuid or a bigint.
If you generate the keys in one database, using a bigint column with a sequence has the advantage that it uses only 8 bytes instead of 16. Also, counting an integer up is cheaper than generating a UUID.
UUIDs shine if keys are generated in several places independent from each other and the resulting ...
0
If you don't really need the data type validation (that ensures that you can't store 'five' in an integer column), you could create a single JSONB column to dynamically store the values
create table user_account
(
id integer primary key,
username varchar(50) not null unique,
attributes jsonb
);
Then you can do things like
insert into user_account (...
1
You could have a table
CREATE TABLE user_info (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users,
property text NOT NULL,
value TEXT NOT NULL
);
and store the additional properties there. That would be the relational way of doing it.
In recent PostgreSQL versions you could also add a jsonb column for ...
3
If all the attributes/columns of that relation/table always contain atomic values (i.e., they accept exactly one value —neither no value, nor two or more, nor NULL marks— of the corresponding simple domain1, 2 per tuple/row), then yes, that relation/table meets first normal form (1NF); otherwise, no, it is not in 1NF, it is unnormalized.
Naturally, I do ...
0
I realise this can be daunting.
but the greatest challenge for schema design is to understand that the world cannot be fluid, but has to rely on certain rules.
So you must build your schema knowing your limitations and making them clear to your coworkers and customers and users.
This is the best way to go forward because in my experinece people sound like ...
2
Your approach is the correct one if the typical workload consists of small transactions that access only a few rows at a time (OLTP).
The extra join won't hurt, because it will be a nested loop join. Databases are optimized for stuff like that.
Any small performance loss will be outweighed by the advantage of having foreign keys to guarantee data integrity....
0
The answer to the question:
Is it possible for a foreign key referencing two different columns for different tables in mysql
is: YES, you can.
This fiddle shows that is possible to reference two tables.
But it has no sense to me, because project.ID=1 and task.ID=1 maybe/surely are not related, and this design doesn't allow to insert any Id in one ...
1
Attribute domain defines the range of acceptable values for an attribute at the logical model level, e.g. "an arbitrary string representing a person's name", "a non-negative integer", "a boolean (true or false) value" etc.
Column specification defines SQL properties of a database column that is used to store the corresponding attribute values in an SQL ...
Top 50 recent answers are included
