18 votes
Accepted

Designing a database for a video game business domain with multiple many-to-many relationships

Yes, the identification of many-to-many (M:N for brevity) associations or relationships is a situation that a database practitioner faces quite commonly when laying out a conceptual schema. ...
MDCCL's user avatar
  • 8,520
14 votes
Accepted

Integrity constraints in a relational database - should we overlook them?

If, as stated in your post, the intention is to create a relational database (RDB for brevity) and, therefore, it is expected that it functions as such, the short answer is: No, you should not ...
MDCCL's user avatar
  • 8,520
9 votes

Integrity constraints in a relational database - should we overlook them?

The basic premise of your developers is absolutely wrong. Foreign keys will impact slightly the performance of the DML of your system. They are not used at all in queries thus have no effect on their ...
TommCatt's user avatar
  • 2,139
9 votes
Accepted

Storing personal data on an invoice to preserve data integrity

The solution I have seen is copying address data to the InvoiceHeader table. This is redundancy, but it is not harmful*. Update anomalies do not occur, because the address on the invoice should not ...
Walter Mitty's user avatar
  • 4,316
7 votes
Accepted

Is combining independent tables called normalisation?

No, the task you are being asked to fulfill —arranging (a) columns that belong in two or more distinct base tables in (b) a single base table— is not called normalisation (nor normalization for those ...
MDCCL's user avatar
  • 8,520
5 votes

How can I optimize this query over this logging table?

You could try a different query: SELECT ItemID FROM (SELECT ItemID ,ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY TimeStamp DESC) rn ,LocationId FROM events ...
RDFozz's user avatar
  • 11.6k
4 votes
Accepted

Do I have to (sometimes) sacrifice normalization for data integrity?

I often come up against the following challenge, and end up de-normalizing my table design in order to enforce data integrity. This sounds like a contradiction. It's normalizing that usually ...
ypercubeᵀᴹ's user avatar
4 votes
Accepted

How to design database tables using normalization in better manner in SQL?

The table structure that your colleagues have suggested might be appropriate for a data warehouse, but it would typically not reduce I/O in an OLTP database. Consider this--if you run a query for all ...
Tony Hinkle's user avatar
  • 7,939
4 votes

Is this really in 3nf?

The example is provided in the answer in the linked question. The author of that answer - and example - states that (Pizza, Topping Type) is a candidate key of the relation - ther's also discussion in ...
ypercubeᵀᴹ's user avatar
4 votes

Storing personal data on an invoice to preserve data integrity

Solution #1 is fine. Keeping an Addresses table is the normalized solution, and has the benefit of keeping your Customer and Invoice tables small. There probably isn't even a need to soft-delete ...
Jon of All Trades's user avatar
4 votes

Normalizing the Test Table

The problem here is the approach. Looking at table values and guessing keys is not a way to go. Simply write down the predicate and constraints for the table, everything else follows from this. ...
Damir Sudarevic's user avatar
4 votes

Why is data integrity not possible when using denormalization?

Data integrity is possible in both normalized and denormalized databases. What is different in the two cases is the number of different ways an application program can write bad data to the database. ...
Walter Mitty's user avatar
  • 4,316
3 votes
Accepted

Redundant FK on parent relationship is denormalization?

Yes, it is denormalization. Any time you're copying data from one location in a database to another location (where the data could easily be obtained from its original location), it's denormalization ...
RDFozz's user avatar
  • 11.6k
3 votes

How can I optimize this query over this logging table?

I can't reproduce the issue. I'm not sure if I did the data prep wrong or the machine I'm testing against is very different than yours. With that said, it may be worth tuning the system instead of the ...
Joe Obbish's user avatar
  • 32.2k
3 votes

Is combining independent tables called normalisation?

I would call that data integration. You're taking data from various sources and integrating it into one nice, clean table. Normalizing or denormalizing it is a separate (but related) decision. Your ...
CalZ's user avatar
  • 914
3 votes

Should I Denormalize Tables To Avoid Joins?

When I think how to design such case, I prefer two-table design. General data and additional/optional information. The general table will contain only not-null columns that are required on most read ...
funfan's user avatar
  • 61
3 votes

Should I Denormalize Tables To Avoid Joins?

I would say that having FirstName and LastName in the User table is still normalized. Having the data in different tables seems excessive, and in practice you'll have a 1-1 relation between the two. ...
Daniel Hutmacher's user avatar
2 votes
Accepted

Is it a good approach to duplicate data in a database?

De-normalisation and certain kinds of data duplication can be a convenient way to speed-up processes. Examples are caching, datawarehousing, and materialised views. Meanwhile the duplicated data is ...
Ezequiel Tolnay's user avatar
2 votes
Accepted

contradiction of 3rd normal form on address table

You should note that not every country has the same rules for addresses. I would believe that address_id (as an integer) is just a surrogate key for an entry in the Address table. It is not a ...
RLF's user avatar
  • 14k
2 votes
Accepted

Need advice on database normalization

Excellent points by @joishi-bodio. Here's a simple diagram explaining the different entities, attributes, and their relationships in your model. The first step in modeling any database is to ...
ruudvan's user avatar
  • 166
2 votes

Integrity constraints in a relational database - should we overlook them?

Changing the title changes the question. FOREIGN KEYs are optional. They do: An FK implicitly create an INDEX in one of the tables. Such an index can be manually added. (So FK is not required for ...
Rick James's user avatar
  • 78.1k
2 votes

Duplicate data or Relationships?

Let's assume your tables are defined like this (by converting your descriptions to SQL Data Definition Language - DDL): CREATE TABLE operator ( id integer NOT NULL PRIMARY KEY, logo_path ...
joanolo's user avatar
  • 13.4k
2 votes
Accepted

ALTER TABLE online by keeping data as BLOBs

That's not what this is saying. Few systems can change a schema without taking the system offline -- even modern databases typically require exclusive table locks and table-rewriting to remove a ...
Evan Carroll's user avatar
  • 63.1k
2 votes

How to design database tables using normalization in better manner in SQL?

Your previous solution is much better. If you need to UPDATE the Mumbai tu Mumbay for example, you do only one operation in the enumeration table and no UPDATEs in the Fact table. And such a easy ...
Matty's user avatar
  • 21
2 votes

Should I Denormalize Tables To Avoid Joins?

Since no one is advising 3 tables I will advise 3 table Join on pk to indexed fk is very fast OP said would search details some times It would avoid dual updates of the data (if in two ...
paparazzo's user avatar
  • 5,043
2 votes

Two primary keys but not a composite, what is this sort of relationship called?

If there two different columns, each of which by itself could be used as a primary key for the table, we say there are two candidate keys. One candidate key is chosen as the primary key. The other ...
Michael Green's user avatar
2 votes
Accepted

Two primary keys but not a composite, what is this sort of relationship called?

It's called a composite key. It doesn't matter how you use a composite key. If two pieces of data uniquely identify a row, it's called a composite key. In fact, the use case you're defining is almost ...
Evan Carroll's user avatar
  • 63.1k
2 votes

Storing Procedure Fields That Were Used

So this seems like largely a question about proper data normalization. With data normalization in mind, we can easily rule out option 1. The data, like you suggest, will clearly be unnormalized and ...
Apneal's user avatar
  • 141
2 votes

Storing Procedure Fields That Were Used

I'm assuming you could have multiple exceptions for a given work/procedure pair, with each exception potentially modifying one or more different procedure fields. While you could certainly store each ...
markp-fuso's user avatar
  • 2,569
2 votes

Storing Procedure Fields That Were Used

Concepts It sounds like you are attempting to build a LIMS system. The ones I have played with contained two sets of tables: One set of tables contains "what you plan to do". Another set of tables ...
Michael Kutz's user avatar
  • 4,829

Only top scored, non community-wiki answers of a minimum length are eligible