Tag Info

New answers tagged

1

You can definitely keep all your dimensions and measures in one fact table and not use any dimension tables. Make sure your OLAP tool supports this though. Normalizing out your dimensions into other tables is done mostly to minimize the size of the fact table, which can get large fast. With no dimension tables you're looking at about 336 MB per year (not ...


1

Normalization is a great concept and essential for well performing relational database. However it can become counter productive. For example you almost never see addresses broken up like this: StateRegionTable CityTable ZipTable StateId CityId ZipId StateDesc CityDesc ZipDesc AddressTable ...


2

You can do this sort of thing by creating what are essentially property bags of these values. This is not an approach that I am a huge fan of, but as with everything in SQL Server it has its place and your scenario might well be that case. This is essentially the EAV approach, and Aaron Bertrand has a great writeup on this here to describe the pros and ...


0

This is exactly the reason for using normalization to limited extent and uafter performance testing. Normalization comes at cost of joins (sorting). Main purpose of DWH on 5NF is to store data safe, not to retrieve it fast. Alternative 1 There is a concept of Materialized View: a view that saved on hard drive. MySQL does not provide it out of the box, but ...


1

OPTION #1 : Use INT UNSIGNED instead of BIGINT If the fields will not exceed 4,294,967,295, change them to INT UNSIGNED ALTER TABLE part1 MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT, MODIFY COLUMN first INT UNSIGNED NOT NULL, MODIFY COLUMN second INT UNSIGNED NOT NULL; ALTER TABLE part2 MODIFY COLUMN link INT UNSIGNED NOT ...


1

It seems that denormalization is done on a case-by-case basis. If you do decide to denormalize your tables for efficiency, make sure you are thoroughly testing your queries. As @Pieter Geerkens mention Every redundancy made for for the purpose of one specific query will slow down all other queries, inserts, and updates against the same data. That is ...


0

They violate 3NF. That's OK when you have a normal model and decide to denormalize here and there judiciously. But not when you model with surrogates to begin with. How do you know you are violating 3NF ? A quote from the book "Using SQLite", Jay A. Kreibich, O'Reilly The Third Normal Form, or 3NF, extends the 2NF to eliminate transitive key ...


1

Assuming a yellow background means that column is part of the primary key, I think Game could use some more attention. First up, homeScore and awayScore shouldn't be part of any key. It is acceptable (though unlikely) that every singe row have the same values for these two columns. Second, id_WinningTeam is can be calculated from the home and away teams ...


6

Your second option is more flexible, but I'm not sure why you're worried about "tons more tables". Usually this would be done with a single table: contact_types ------------- id (PK) name contact_details --------------- id contact_type_id (FK to contact_types.id) value organization_contacts --------------------- id (PK) contact_detail_id ...


4

I personally like the second option best. This is a fairly common way to solve this problem. Consider adding a "default_contact_type_id" column to your organization table for quicker lookups and/or reporting. Option 3: For the sake of completeness, you could add an XML column to the organization table calles simply "contact_information". An XML blob can ...


1

The theory is all fine and good, but it only starts to really make sense once you understand the practice. The Lawyer's version of the first three Normal Forms is: The Key - there must be a Primary key for every relation being normalized. The Whole Key - There must not be any functional dependencies of attributes on any proper subset of the Primary Key. ...



Top 50 recent answers are included