Your senior colleague is a developer, not a data modeller. You are better off starting from scratch, without them. Normalisation is complicated only to those who will not read books. It is fair enough that he makes you think, but some of the issues are absurd. Your numbers: You need to appreciate the differences between … Read more
You want to start designing a normalized database up to 3rd normal form. As you develop the business logic layer you may decide you have to denormalize a bit but never, never go below the 3rd form. Always, keep 1st and 2nd form compliant. You want to denormalize for simplicity of code, not for performance. … Read more
The two most common reasons to denormalize are: Performance Ignorance The former should be verified with profiling, while the latter should be corrected with a rolled-up newspaper 😉 I would say a better mantra would be “normalize for correctness, denormalize for speed – and only when necessary”
Well, if I had to explain it to my wife it would have been something like that: The main idea is to avoid duplication of large data. Let’s take a look at a list of people and the country they came from. Instead of holding the name of the country which can be as long … Read more
Keep a friend table that holds the UserID and then the UserID of the friend (we will call it FriendID). Both columns would be foreign keys back to the Users table. Somewhat useful example: Table Name: User Columns: UserID PK EmailAddress Password Gender DOB Location TableName: Friends Columns: UserID PK FK FriendID PK FK (This … Read more
Right, the problem is you want only one object of one sub-type to reference any given row of the parent class. Starting from the example given by @Jay S, try this: create table media_types ( media_type int primary key, media_name varchar(20) ); insert into media_types (media_type, media_name) values (2, ‘TV series’), (3, ‘movie’); create table … Read more
A 1:1 relationship typically indicates that you have partitioned a larger entity for some reason. Often it is because of performance reasons in the physical schema, but it can happen in the logic side as well if a large chunk of the data is expected to be “unknown” at the same time (in which case … Read more
Your pizza can have exactly three topping types: one type of cheese one type of meat one type of vegetable So we order two pizzas and choose the following toppings: Pizza Topping Topping Type ——– ———- ————- 1 mozzarella cheese 1 pepperoni meat 1 olives vegetable 2 mozzarella meat 2 sausage cheese 2 peppers vegetable … Read more
You can use a lookup table with a VARCHAR primary key, and your main data table uses a FOREIGN KEY on its column, with cascading updates. CREATE TABLE ColorLookup ( color VARCHAR(20) PRIMARY KEY ); CREATE TABLE ItemsWithColors ( …other columns…, color VARCHAR(20), FOREIGN KEY (color) REFERENCES ColorLookup(color) ON UPDATE CASCADE ON DELETE SET NULL … Read more
Re “atomic” In Codd’s original 1969 and 1970 papers he defined relations as having a value for every attribute in a row. The value could be anything, including a relation. This used no notion of “atomic”. He explained that “atomic” meant not relation-valued (ie not table-valued): So far, we have discussed examples of relations which … Read more