Make both user_id and album_id in photo nullable, but add a check constraint that makes sure that exactly one of them is set. If you cannot change both attributes in the same statement, you can use a deferred constraint trigger instead of the check constraint.

As you have discovered, the decomposition of R in the two relations R1(B, C) and R2(C, A) is a lossless decomposition (and both relations are in BCNF). On the other hand, the dependency AB -> C is not preserved by this decomposition. Note that it is not difficult to convince yourself that, in this particular case, a decomposition of R cannot maintain all the three attributes together, since we have C -> A, and by joining the decomposed relations the dependency AB -> C cannot be obtained in any way. This example simply shows that there are cases in which every possible algorithm to decompose a relation in BCNF can produce the loss of one or more dependencies.

1NF - Does it have a repeating group? None of your table has. 2NF - Every non candidate-key attribute must depend on the whole candidate key This seems a bit controversial. Your main table seems to have multiple candidate keys. And, you also defined an surrogate ID. I'm not fully sure the requirements. So, it automatically satisfies 2NF because every non non candidate key attribute depends on the primary key. This depends on the requirements. You need to know the candidate key but that's a part of the requirements. Is the variety unique among within items? do you need a category ID ? Isn't the category inferred from the product (and therefore you're missing a relation between category and product)? The full requirement is needed. Based on what you said, it's not 3NF. There is a transitive dependency in the main table (category_id can be inferred from variety name, which is not a key, item_id can be inferred from category_id).

This is a bit of an opinion based question, but if you have a natural primary key for an entity, you might as well use it. To avoid wasting space, you could convert the hexadecimal string to a number.

Consider a parent_id column to use for hierarchy. This would be similar to the common concept of an employee table. With an employee structure, you would not create a table for reach tier of employment. In this structure, all standards would be the in same table. However, sub-standards would have a value in the parent_id column pointing them back to the Standard they are a child of. Below is an example of how the data would be stored. Any standard with a NULL parent_id can be considered a top level standard. ID ParentID Code Description 1 NULL 7DF1 Description of some standard blah 2 NULL 7DF2 Description of some other standard asdf 3 NULL 8A33 Yet another description 4 3 a Description of 8A33 part a 5 3 b Description of 8A33 part b

I would consider separate tables. Think about all of the data analysis you may ever need to do in the future, and plan a schema that supports that. With single counts in the post_stats table, you'll only ever know a post was viewed x time per day, or y times overall, etc.. Instead, consider dropping that table all together, and creating a tables called Views and Likes. For example. the Views table could be foreign keyed to the posts table. In this table you could store details such as... view_id post_id viewed_by datetime_viewed With this schema, you could query total views by post or by user, and for any time period you choose. For example... views by post for today views by user for the past week views of user's posts for the past week etc.. You could then create various views to return stats from those tables, as you see the need. Going with this strategy, you'll will not be limited on what you can analyze later. Additionally, it would would just be a simple insert anytime an action happens, vs an update that may conflict with another simultaneous update. The same concept would apply for Like, etc..

It is possible to create an index for fields within an array of composite type, but the applicable https://www.postgresql.org/docs/current/indexes-types.html#INDEXES-TYPES-GIN for arrays is GIN. And https://www.postgresql.org/docs/current/gin-builtin-opclasses.html that would work for your query predicate WHERE (co[*].r) BETWEEN 9.65 and 9.67. So not possible after all - with current Postgres 14, and probably future versions as well. The problem is merely academic, though, because you wouldn't do that to begin with. The solution is a proper relational design. Whenever you find yourself creating a table with an array of composite type, cease and desist. With a probability bordering on certainty, you should create a separate table instead, https://en.wikipedia.org/wiki/Database_normalization your design at least that much. Then everything becomes rather simple. Could look like this: -- parent table optional, not needed for demo CREATE TABLE tbl2_parent (tbl2_id serial PRIMARY KEY); -- more attributes? -- random test data INSERT INTO tbl2_parent SELECT FROM generate_series(1, 1000) t; -- main table CREATE TABLE tbl2( tbl2_id int NOT NULL -- REFERENCES tbl2_parent , idx int NOT NULL , l float8 NOT NULL , r float8 NOT NULL , PRIMARY KEY (tbl2_id, idx) ); -- random test data INSERT INTO tbl2 (tbl2_id, idx, l, r) SELECT t , i , random() * 100 , random() * 100 FROM generate_series(1, 1000) t, generate_series(1, 5) i ORDER BY 1, 2; Now, the index is simple: CREATE INDEX tbl2_r_idx ON tbl2 (r, tbl2_id); Any B-tree index with leading r does the job. Minor additional optimizations depend on the complete use case. And the query is something like: SELECT DISTINCT tbl2_id FROM tbl2 WHERE r BETWEEN 9.65 and 9.67; Or, if you need more than distinct IDs: EXPLAIN ANALYZE SELECT * FROM tbl2_parent p WHERE EXISTS ( SELECT FROM tbl2 t WHERE t.r BETWEEN 9.65 AND 9.67 AND t.tbl2_id = p.tbl2_id ); db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=fd7ebbc39240674113d2475252cc7758 Either query can use very fast index-only scans with tables that are vacuumed enough, or at least index scans. Asides For your original scenario, you could just use the built-in type point. It consists of two float8 quantities, exactly like your custom row type. Reference first and second number with a subscript like: SELECT ('(7,8)'::point)[0]; -- 7 See: https://stackoverflow.com/a/8150944/939860 Your query in your original test case can be rewritten as: SELECT * FROM tbl2 t WHERE EXISTS ( SELECT FROM unnest(t.co) elem WHERE (elem.r) BETWEEN 9.65 and 9.67 ); Reading your rationale, though, you might consider https://postgis.net/ first, or at least mainline https://www.postgresql.org/docs/current/datatype-geometric.html .

There should be a university table to which the auditorium table should refer rather than repeating the uni's data for each auditorium of that uni.

Yes, you can use an https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION . You'll need to install the additional module https://www.postgresql.org/docs/current/static/btree-gist.html to add an operator class we need for the multicolumn index backing the constraint. There is no data type "datetime" like you display. Your values indicate date, so I'll go with that. (The actual data type matters for the implementation!) Also, your table design with just valid_from generally works. valid_to can be derived from the next row. But for the purpose of an exclusion constraint, we need valid_to in the same row. So add that (redundantly). ALTER TABLE person_email ADD CONSTRAINT person_email_no_username_overlap EXCLUDE USING gist (username WITH =, daterange(valid_from, valid_to, '[]') WITH &&); This way, the same username can never be in use in overlapping time ranges. Note how I made upper and lower bound inclusive with '[]'. Adapt to your needs. There are subtle differences for different data types. NULL as lower or upper bound means "unbounded". Alternatively, you could use a daterange column to begin with. Either has pros and cons. Related: https://stackoverflow.com/a/56690941/939860 https://stackoverflow.com/a/67612325/939860 https://stackoverflow.com/a/15305292/939860 https://dba.stackexchange.com/questions/193688/composite-constraint-of-numrange-and-boolean/193691#193691

A single "fat" table will be more efficient than joining 10 separate tables. While the disk IO will be the same in principle, a view will need to perform 9 joins - essentially 9 large sort/merge operations. Materialized views ( https://www.postgresql.org/docs/14/sql-creatematerializedview.html ) makes it easy to separate the physical layout of the 'insert/update' and 'select' parts of the application. create materialized view consolidated_stock_view as select a.pk, a.measure_1, b.measure_2, c.measure_3 -- etc from a_table a join b_table b on a.pk = b.pk join c_table c on a.pk = c.pk -- etc When you are finished loading data into the 10 'fact' tables, refresh the materialized view refresh materialized view consolidated_stock_view;

Something like this should do the trick (note the aliases, and feel free to adjust the casing to your preferred format): SELECT Posts.ID, SUM(CASE WHEN Likes.ID IS NULL THEN 0 ELSE 1 END) AS TotalLikesCount, SubmitterProfiles.USERNAME, MAX(CASE WHEN Likes.PROFILE_ID = 98765 THEN 1 ELSE 0 END) AS DidRequestingUserLikeThisPost FROM USERPOSTS AS Posts INNER JOIN USERPROFILES AS SubmitterProfiles -- Inner Join should since a Post must be submitted by a User Posts.PROFILE_ID = SubmitterProfiles.ID LEFT JOIN USERVOTES AS Likes -- Outer (Left) Join because not all Posts necessarily have Likes ON Posts.ID = Likes.POST_ID LEFT JOIN USERPROFILES AS VoterProfiles -- Need to continue with an Outer (Left) Join here because we're pulling the profile of the User who liked the Post (if there is a like) ON Likes.PROFILE_ID = VoterProfiles.ID WHERE Posts.THREAD_ID = 12345 GROUP BY Posts.ID, SubmitterProfiles.USERNAME Please read the comments for more information, but basically this starts with your USERPOSTS table and inner joins your USERPROFILES table (which should always have a many-to-one relationship), then outer joins the remaining information (because some posts may not have likes). Since the relationship between USERPOSTS and USERVOTES is one-to-many (when there are likes) the results set becomes a single row per like, so we need to group up by the ID of the post so we can count how many likes (rows) there are which I'm doing using the SUM() function with a CASE statement to handle NULL cases (when there's no likes for a given post). We also have to group by the USERPROFILES.USERNAME so that we can SELECT it and this doesn't change the cardinality of our results, since every post can only be submitted by a single user.

I've seen it mentioned in blogs and tutorials that a weak entity "doesn't have a primary key," but this is misleading. You are correct that every table needs a primary key. This is necessary for the table to qualify as a relation. Every relation has one or more candidate keys, and one of the candidate keys is chosen as the table's primary key. The meaning of a candidate key is that the columns are non-NULL and they can be used to uniquely identify any row in the table. The term weak entity is used for a table where the rows cannot exist without referencing some "parent" entity. For example, you might have tables Users and Phones such that a user can have zero, one, or multiple phones. But a phone number cannot exist without referencing its owning user. Therefore Users is a strong entity; one row in the Users table can exist without regard to how many phones it has, or even if no phones reference it. Phones is a weak entity. A given phone number cannot exist unless it references a row in Users. A good way to implement this in SQL is to require that the primary key of the Phones table include a reference to the primary key of a row in User. But a user may own multiple phones, so there needs to be an additional column in Phones to make it a compound primary key. This is often called the discriminator and it's just an arbitrary value that serves to be distinct for each of the phones belonging to a given user. It doesn't necessarily have any other meaning, but sometimes people use it as an ordinal number for displaying the phones. Example: CREATE TABLE Phones ( owner_id INT NOT NULL, phone_ordinal INT NOT NULL, phone_number VARCHAR(32) NOT NULL, phone_type VARCHAR(16) NOT NULL, PRIMARY KEY (owner_id, phone_ordinal), FOREIGN KEY (owner_id) REFERENCES Users(user_id) ); Some people state this as "Phones doesn't have a primary key" but they mean it doesn't have its own auto-incrementing integer column. It depends on values in the primary key of the Users table. Many software developers confuse "primary key" with "an auto-incrementing integer column named id." This is a misunderstanding of what a primary key is. A primary key is a constraint applied to one or more columns, which together are a candidate key for the table. It does not necessarily mean the primary key is a single column, or that it is an integer, or that it is auto-incrementing. P.S. I want to be clear that using auto-increment for a primary key is not the criterion for a strong entity. Auto-increment is not required by the relational model at all, it's just a convenience that is popular.

Assuming that the FD are a cover of the functional dependencies of R, you can find a minimal cover of them: B C -> A B C -> E C -> D From this minimal cover, one can find easily the only candidate key, which is {B, C}. As you have correctly said, the dependency C -> D violates the BCNF, so we can decompose R in R1, R2: R1 = {C D}, with the the projected dependency C -> D, and C the only candidate key R2 = {A B C E}, the projected dependencies B C -> A, B C -> E, and the only candidate key B C Both R1 and R2 are in BCNF.

To normalize in 3NF one should start from a canonical cover of the functional dependences. In this case one is: { A → C A → E A → H B → C B → G C → D C → F } So a decomposition in 3NF with the “synthesis” algorithm is: R1 < (A C E H) , { A → C E H } > R2 < (B C G) , { B → C G } > R3 < (C D F) , { C → D F } > R4 < (A B) , { } > Starting from R, a possible decomposition in BCNF with the “analysis” algorithm is: R1 < (B G) , { B → G } > R2 < (A B) , { } > R3 < (C D F) , { C → D C → F } > R4 < (A C E H) , { A → C A → E A → H } > Note that with this decomposition the dependency B → C is lost.

There are probably more than one way to do this, but here is one idea. You don't have to use triggers. Use https://mariadb.com/kb/en/create-sequence/ to generate unique numbers - assuming it's OK that the numbers are created sequentially. Assuming you are designing a system for one particular bank, and the digits in the sort code are used to identify the bank and branch, then: The first two digits of the sort code identifies the bank and will always be the same, so you don't need to store these. Put the branch digits in the same column as the account number digits. Create one sequence per branch where you specify a unique range for each, defined by the sequence's MINVALUE and MAXVALUE. For display purposes, you can decode into sort code and account number by using https://mariadb.com/kb/en/generated-columns/ . Basic example code for sequences CREATE SEQUENCE seq_branch_1 MINVALUE 100000000 MAXVALUE 199999999 START WITH 100000000 INCREMENT BY 1; CREATE SEQUENCE seq_branch_2 MINVALUE 200000000 MAXVALUE 299999999 START WITH 200000000 INCREMENT BY 1; And so on. At the time of creating an account you presumably know which branch it's for, so: INSERT INTO account(customer_id, account_type_id, ... unique_account_number, ...) VALUES (416, 2, ..., NEXTVAL(seq_branch_2), ...);

It always depends. But I would say that the most right is an entity of Pessoa that can even PessoaFisica and PessoaJuridica find yourself interesting and have other objects with papers that this person exercises in your organization. Almost always having only the roles with repetition of my data of the person in these entities is wrong by keeping different objects in the system for the same real object.The person is a clear and obvious object of any model. Paper data is not always so obvious, we have to think about it as special documents, because they don't even exist as real objects.Then you should have tables Cliente, Colaborador, Fornecedor, etc. even because the same person can have more than one role.Conceptually it is the most correct to have everything separate. If for performance reasons you do not want to have separated even you could have the roles next to the object of the person, but this is not universally more interesting nor even looking only performance. But every time this is less necessary because of technologies that behave closer to how memory is. Mostly on disk it may have useful to kill a table only, but it is not easy to administer this.In memory it would usually have to keep these objects apart and there does not have much advantage joining everything.But what is most worth is the correct concept, with it gives to do whatever needs in an organized and easy way, when the concept is wrong all maintenance becomes complicated or almost impossible, unless by changing everything completely.What is almost always wrong is having the paper tables and not having the related person, that is, repeating the same person in all tables, as almost all systems do, a pity.See https://pt.stackoverflow.com/q/151323/101 .

The relationship you are trying to establish has not become clear. If I understood well I believe that it is a 1:1 relationship between the tables. Then you could launch an SQL like this:SELECT end_origem.rua, end_origem.latitude, end_origem.longitude, end_destino.rua, end_destino.latitude, end_destino.longitude FROM tb_solicitacao s INNER JOIN tb_origem o ON s.id_origem = o.id INNER JOIN tb_destino d ON s.id_destino = d.id INNER JOIN tb_endereco end_origem ON o.id_endereco = end_origem.id INNER JOIN tb_endereco end_destino ON d.id_endereco = end_destino.id WHERE s.id = 1; This simple example relates to the table requests with the source and destination tables and their due addresses. Notice the use of INNER JOIN, the query says that you want to rescue all the records that contain in the requested table and mandatory (INNER JOIN) have records that relate to the attribute id_origin and id_destination in their respective tables, as well as the tables tb_origin and tb_destination must necessarily relate to the table _ by attribute id_address. INNER JOIN will only rescue if it contains records in all the tables of the relationship; If a request exists, there must be a origin and destination as well as their respective addresses.Let's say that there is a new table that may or may not contain related records, in which case the use of LEFT JOIN. I set this example based on your querie above, but did not make any modeling on the bank and nor test. Adapt to your taste! The concept to relate using normalized structures is this, I do not advise to follow another path as much as it seems easy, there in front will generate serious problems about expansion and maintenance.Hugs.

Well let's see... seeing this possible alternative you show in the question, I get that conclusion. .It is in 1FN, because all fields contain only atomic and monovalued values, that is, it does not contain repetition groups.It is also in 2FN, because there is no non-key attribute that is dependent on only one part of the primary key.And it is in 3FN too, because there is no key attributes being dependent on other non-key attributes.So I would say it's in 3FN yes.

Supposing we want to relate which dogs have which owners and vice versa. We have that the same person can own several dogs and a dog can have more than one owner. There are also dogs without owners and people who do not have dogs.We could implement in the table of people, a field with the ids of all the dogs it has and in the table of dogs a field with the ids of all its owners. That's possible, but it's a 1FN violation.Therefore, in order to reach 1FN, the multivalued fields must be eliminated. We will have to create a table that says which are the puppies of each person and one that are the owners of each dog. It turns out that these tables are actually one. Therefore, we use an intermediate table, where a relationship M:N between tables A and B is implemented through a new table C where we have relationships 1: between A and C and 1: between B and C. That is, the relationship M:N was broken into two relationships, one 1: and one 1:. In the table C, there is no field beyond the two foreign keys and both are part of the primary key.Soon, if the relationships M:N should be decomposed (and therefore eliminated) to reach 1FN, so there is no way they persist when arriving at 2FN, 3FN or any other normal beyond.

Normalization exists essentially to resolve redundancies. Do you see any redundancy there?AddressNo shows. Is it possible for the customer to have more than one address? Is it possible more than one client have the same address? If you can, maybe make sense to apply normalization in this case. With only one address it makes no sense to make this separation. It does not apply either the second or any normal form.Note that in the second example created a table called Cliente Endereço. Why are you putting an agency's address on it? It doesn't make sense.Let's assume that actually it is a general address table. I'd even put the address there. But what for?Nothing prevents having a table only for addresses, but if there is no repetition of the data is not doing this by normalization. This makes sense if entities can have more than one address or more than one of them have the same address.I did not enter the normalization of the address table because it does not appear to be the focus of the question and in the presented form it may even be normalized, has no data indicating what are the columns, not even the types, could very well be ids standard data. It may also be that you don't want to normalize this.AgencyA table was created to separate the agency that it belongs to. Again, do you have more than one agency the client can have? It's the same question as the address. What do you think you did this? What problem do you think solved? Normalization needs to solve problems, not cause new ones. I didn't see the advantage.Best modelEven these cases can be questioned in modern databases. It does not cost so expensive to maintain space for more than one address in the entity itself. It is not always a problem to have a very possible repetition of registration of the address when two entities are in the same address. It's a matter of pragmatism.If you can have these cases, strictly speaking, it should normalize, but an experienced developer will analyze how much effort is worth because it complicates the model by making all code and performance difficult. So you have to think if it's worth it, if it's so necessary.On the other hand, the whole model could be wrong. This idea of separating entities in client, supplier, bank, etc. is wrong by nature. At least in the form presented.Entities are physical and legal persons (separated). Customer, supplier, bank, carrier, seller, employee, etc. are roles that these people exercise in this organization. These tables should only have data on the roles. The data of the person itself should be in the tables of physical and legal persons. This is the most correct, but again it is possible to pragmatically do otherwise if it makes sense. But you need to choose another way because it's the best and not because it's the only way you know it. Not all cases make sense to separate like this, but in general it is the most correct.I could point out other possible errors in this model. But it would be speculation because it is only right or wrong knowing all the requirements. The mistakes I see would be according to my experience, not with the real case, they could be right or not. https://pt.stackoverflow.com/q/151323/101 To know how to normalize you need to know the goals. To be the most correct formally, to be the fastest to develop, to be the easiest to give maintenance, to be the most performing, to be what the teacher taught or the boss even had it not the best, or whatever. You can't normalize blindly. As far as normalizing and where to stop is something you will learn from time. https://pt.stackoverflow.com/q/54177/101 https://pt.stackoverflow.com/q/14839/101 https://pt.stackoverflow.com/q/80948/101 https://pt.stackoverflow.com/q/154748/101