Tag Info

Hot answers tagged

26

You should go as far as you should, and no further. Of course. ~ The problem may be that this is a bit of an art, and it's why this isn't a pure science. Our main product is an analysis and reporting system, and so in that regard, we have quite a few detail records. We initially had it designed with lots of joins on a common ID for some of the child ...


26

It seems you are asking if denormalization makes sense. Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data. In some cases, denormalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access ...


15

One reason for normalisation is to remove data modification anomalies ORMs usually do not support this. I have many examples of Hibernate-designed databases that break this principle: bloated (string repeated over 100s of millions of rows) no lookup tables (see above) no DRI (constraints, keys) varchar clustered indexes unnecessary link tables (eg ...


14

Normalization absolutely is used in the real world... and hopefully you know that 3NF is only the third one of... what is is now, 8? But 3NF should be an easy target. However... I would venture to say that there could not be such a tool. Normalization, technically, is an attribute of each table. Within a given database, different tables may have ...


13

1NF ⊂ 2NF ⊂ 3NF ⊂ BCNF ⊂ 4NF ⊂ 5NF ⊂ 6NF


13

If it fits within the rules of normalization, then 1:1 relationships can be normalized (by definition!) - In other words, there is nothing about 1:1 relationships that make it impossible for them to obey the normal forms. To answer your question about the practicality of 1:1 relationships, there are times when this is a perfectly useful construct, such as ...


12

The other database designer is simply wrong, but your reasoning is wrong as well. Assume you start with this table, which has a single candidate key, "game_title". Table: game_titles game_title year_first_released -- The first game 1998 The second game 1999 Best game: the third one 2001 The ...


12

Normalization is concerned with all Candidate Keys. A Primary Key is just a candidate key. Primary keys are no different to any other candidate key. Potential confusion arises because in the early days of relational database theory the term Primary Key used to mean any and all candidate keys whereas modern usage is that Primary Key means only one key that ...


11

A database in 3NF is in 2NF also. A simple mnemonic is: The key The whole key and nothing but the key So help me Codd 2NF states that a table contains no fields that are logically a function of a part of the primary key. 3NF states that a table contains no fields that are logically a function of any field of the table but the 'whole' key. 3NF can be ...


11

it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright. Surrogate keys don't break 2NF. 2NF says "If a column is dependant on only part of a multi-valued key, remove that column to a separate table." They stipulate that it's just better to have everything as one big table no ...


10

Following are the resources which I'm using to brush-up my database skills and to teach newbies... Animated DataBase Courseware From Database Design to Advanced Concepts wofford-ecs.org Database Design - Normalization db4u Normalization exercises http://www.sql-ex.ru/ Tons of sql exercises ...


10

Proper solution The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this: CREATE TABLE name ( name_id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, verified BOOLEAN NOT NULL DEFAULT FALSE, UNIQUE (name, email) ); ...


9

There are few things to consider other than normalization. For instance, you have a column for AGE. Are you going to update that every year? How will you know when to do that? The same goes for years of experience. There are some columns that will probably have multiple values for each applicant: School, Course, etc. You may also want to check your ...


9

1NF requires that every attribute position in every tuple in every relation contains a single value of the appropriate type. The types can be arbitrarily complex. In fact, the types can be relations. (CJ Date's book Database in depth: relational theory for practitioners treats this issue in a way that's pretty easy to understand.) "Atomic" has never really ...


9

The more things change, the more they stay the same. There have always been lazy developers who cut corners or just don't know or want to follow best practices. A lot of the time they can get away with it in smaller applications. It used to be jamming COBOL-inspired data structures into early RDBMS, or the God-awful mess that was dBase. Now it's ORMs and ...


9

This sounds like a really simply one-to-many relationship. For SQL Server, I would write this like: CREATE TABLE Devices ( DeviceID INT , DeviceName nvarchar(255) ); CREATE TABLE Cards ( CardID INT , CardName nvarchar(255) , DeviceID INT ); CREATE TABLE Ports ( PortID INT , PortName nvarchar(255) , CardID INT ); INSERT ...


8

Yes, that's the way it's defined.


8

I agree that memory constraints did bear a direct correlation to normalization... Memory constraints still matter. Quantity isn't a problem, speed is. CPUs aren't getting any faster at the moment (We're getting more core's, not cycles per second) Modern CPU architectures attempt to overcome the speed limitation by providing separate memory for each ...


8

Another way (without Nulls) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE constraint on TableB. @Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you sane) if you don't have Id columns all over your tables ...


7

To answer your question, yes normalization is needed. Common sense is a relative term and is open to interpretation. RDBMS's have been around since the 1970's. Normalization has been put into use on countless projects over the past 30 years, much to the benefit of the applications being developed. The most complex time-consuming problems I have worked ...


7

You should use a lookup table Not all clients will use the ENUM in the application You will get a reporting or MIS or Excel app connecting at some point How can do "NOT EXIST" otherwise? You will be asked this You won't know about client enum changes Strings are inefficient compared to a tinyint, especially when you need to index it for your WHERE clause ...


7

There's a little aphorism that goes: The key (1NF) The whole key (2NF) And nothing but the key (3NF) . . . So help me Codd In your example we can assume 1NF to begin with as the relational structure doesn't imply any repeating groups within the row (i.e. no D1, D2, D3 etc.). R = {a, b, c, d, e, f, g} F = {AB --> C, A --> DE, B --> F, F --> GH, D ...


7

Arguably, it doesn't. Adding a surrogate key is an implementation decision (to respect how the RDBMS works) taken at implementation time. During modelling and normalisation, you should end up with BCNF (slightly stricter and more correct 3NF) without surrogate keys That is, introducing surrogate keys at the start of the design process is wrong. Even though ...


7

It isn't necessary to have a surrogate primary key on your clubs_chains table. The combination of the two foreign keys in clubs_chains is adequate for the primary key. You can use a foreign key constraint to ensure that your clubs_chains_paymethod table references an existing record in clubs_chains using the compound primary key. This might be helpful ...


7

You could consider a cart to be a storage location. Simply add attributes to the table so that a location can be identified as type "cart" or "shelf", like so: If there's a requirement for a hierarchy - for example, if a cart might be stored in a storage location itself - you could define the hierarchy within your storagelocation table. This is not a ...


6

You would be better to rewrite the query as: SELECT payments.* FROM customers JOIN payments ON payments.id_customer = customers.id WHERE customers.id_project = 5 While this seems less concise and a good query planner will see what you are trying to do and run your correlated sub-query as the above join instead, a bad query planner may end up ...


6

Normalization is a goal only when it supports your data model well enough to warrant it. It is meant to be a guide to allow growth, management and maintainability. Remember that the book on normalization, nor its writer are going to build or maintain your database or its application. A good read on the subject of "too much normalization" is here. And, yes ...


6

Every rule, every process, every pattern that is taught in programming courses is an effort to try to "institutionalize" common sense. If all of your developers have perfect common sense at all times and are clear-headed and insightful, then you don't need to follow anybody's rules, processes or patterns. However, as the saying goes: "Common sense ain't" - ...


6

Generally, I don't add redundant columns unless I really need too. Running a COUNT over a set of data is quite efficient in any RDBMS. Consider this is a read over indexed (hopefully) cached data to get the count will beat the the 2nd write in to maintain the denormlaised column. This write requires more resources/locking/longer transaction etc which ...


6

I would suggest not logging the "latest activity" but rather keeping a full audit trail. In order to minimize space requirements, you might want three tables: CREATE TABLE dbo.Users ( UserID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 users Username NVARCHAR(128) NOT NULL UNIQUE, /* , other columns */ ); CREATE TABLE dbo.Tables ( ...



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