True or False: Good design calls for every table to have a primary key, if nothing else, a running i

Posted by Velika on Stack Overflow See other posts from Stack Overflow or by Velika
Published on 2010-03-06T00:31:09Z Indexed on 2010/05/18 2:40 UTC
Read the original article Hit count: 219

Filed under:

data-warehouse

|

database-design

|

best-practices

Consider a grocery store scenario (I'm making this up) where you have FACT records that represent a sale transaction, where the columns of the Fact table include

SaleItemFact Table
------------------
CustomerID  
ProductID  
Price  
DistributorID  
DateOfSale  
Etc  
Etc  
Etc

Even if there are duplicates in the table when you consider ALL the keys, I would contend that a surrogate running numeric key (i.e. identity column) should be made up, e.g., TransactionNumber of type Integer.

I can see someone arguing that a Fact table might not have a unique key (though I'd invent one and waste the 4 bytes, but how about a dimension table?

© Stack Overflow or respective owner

Related posts about data-warehouse

PostgreSQL to Data-Warehouse: Best approach for near-real-time ETL / extraction of data

as seen on Stack Overflow - Search for 'Stack Overflow'
Background: I have a PostgreSQL (v8.3) database that is heavily optimized for OLTP. I need to extract data from it on a semi real-time basis (some-one is bound to ask what semi real-time means and the answer is as frequently as I reasonably can but I will be pragmatic, as a benchmark lets say we… >>> More
Time and date dimension in data warehouse

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm buildind an data warehouse. Each fact has it's timestamp. I need to create reports by day, month, quater but by hours too. Loking at the examples I see that dates tend to be saved in dimension tabels. But I think, that it makes no sense for time. The dimension table would grow and grow. On the… >>> More
Oracle Healthcare Data Warehouse Foundations RELEASED!

as seen on Oracle Blogs - Search for 'Oracle Blogs'
Since I joined Oracle I've been working on Oracle Healthcare Data Warehouse Foundations (OHDF). It was officially released earlier this month at HIMSS. But for over 2 months prior to that I had to keep it a secret. It was so tough; I didn't even tell my family when they asked me what I was working… >>> More
BIWA Wednesday TechCast Series - Opposition to Data Warehouse Initiatives

as seen on Oracle Blogs - Search for 'Oracle Blogs'
BIWA Wednesday TechCast Series - 19th Event! Opposition to Data Warehouse Initiatives Please join us for this webcast on Wednesday, March 24, 12 noon Eastern or check your local area's time Webcast is open to clients, prospects and partners. No matter how good your technology and technical skills… >>> More
OpenWorld on your iPad and iPhone

as seen on Oracle Blogs - Search for 'Oracle Blogs'
Most of you probably know that each year I publish a data warehouse guide for OpenWorld which contains links to the latest data warehouse videos, a calendar for the most important sessions and labs and a section that provides profiles and relevant links for all the most important data warehouse presenters… >>> More

Related posts about database-design

(Database Design - products attributes): What is better option for product attribute database design

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I new in database design. What is better option for product attribute database design for cms?(Please suggest other options also). option 1: 1 table products{ id product_name color price attribute_name1 attribute_value1 attribute_name2 attribute_value2 attribute_name3 attribute_value3 } option… >>> More
Book Review: Pro SQL Server 2008 Relational Database Design and Implementation

as seen on SQL Blog - Search for 'SQL Blog'
Investing in proper database design is a very efficient way to cut maintenance costs. If we expect a system to last, we need to make sure it has a good solid foundation - high quality database design. Surely we can and sometimes do cut corners and save on database design to get things done faster… >>> More
Advice on database design / SQL for retrieving data with chronological order

as seen on Stack Overflow - Search for 'Stack Overflow'
I am creating a database that will help keep track of which employees have been on a certain training course. I would like to get some guidance on the best way to design the database. Specifically, each employee must attend the training course each year and my database needs to keep a history of… >>> More
Fiscal year handling strategies in database design

as seen on Stack Overflow - Search for 'Stack Overflow'
By fiscal year I mean all the data in the database (in all tables) that occurred in the particular year. Lets say that we are building an application that allows user to choose from different years. What way of implementing this would you prefer, and why: Separate fiscal year data based on multiple… >>> More
Database design for summarized data

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a new table I'm going to add to a bunch of other summarized data, basically to take some of the load off by calculating weekly avgs. My question is whether I would be better off with one model over the other. One model with days of the week as a column with an additional column for price… >>> More