137

It's never a bad idea to have a guaranteed unique row identifier. I guess I shouldn't say never – but let's go with the overwhelming majority of the time it's a good idea. Theoretical potential downsides include an extra index to maintain and extra storage space used. That's never been enough of a reason to me to not use one.


93

I disagree with all the answers before. There are many reasons why it is a bad idea to add an auto increment field in all tables. If you have a table where there are no obvious keys, an auto-increment field seems like a good idea. After all, you don't want to select * from blog where body = '[10000 character string]'. You'd rather select * from blog where ...


60

Autoincemental keys have mostly advantages. But some possible drawbacks could be: If you have a business key, you have to add a unique index on that column(s) too in order to enforce business rules. When transfering data between two databases, especially when the data is in more than one table (i.e. master/detail), it's not straight-forward since sequences ...


56

I think you misunderstood what indexing does for database performance. An index helps the database find rows. Indexes are specialized data-structures that, in exchange for extra disk-space and some performance when inserting and updating, help the database engine home in on matching rows. Because they take extra space and cost (a modicum of) performance to ...


35

Option #2, using reference tables, is the standard way of doing it. It has been used by millions of programmers, and is known to work. It is a pattern, so anyone else looking at your stuff will immediately know what is going on. There exist libraries and tools that work on databases, saving you from lots and lots of work, that will handle it correctly. The ...


34

Yes. In fact, you very well may need to pay more attention to your indexes. Normalization is about optimal storage. That's often at odds with retrieval speed, as more complex queries with complicated joins are used. Sometimes people maintaining databases that require fast retrieval speeds will de-normalize, or arrange their data in slightly less ...


30

Yes, there are tons of reasons why this may be the better design. You may have an inheritence/extension relationship, e.g. you might have a User table and then an Administrator table which has more fields. Both tables may have a primary key of User ID (and therefore have a 1:1 relationship) but not all users will have a record in the Administrator table. ...


20

Just to be contrary, No, you do NOT need to always have a numeric AutoInc PK. If you analyse your data carefully you often identify natural keys in the data. This is often the case when the data has intrinsic meaning to the business. Sometimes the PKs are artefacts from ancient systems that the business users utilize as a second language to describe ...


16

The approach is, frankly, horrible. As I understand it, you want the column 'Price' to be interpreted as a foreign key to Product.Id, and then "redirect" the query for Pear prices to a query for Apple prices or for Orange prices. This requires complicated logic in your SQL code for what should be a straightforward table look-up, and since data base schema ...


15

Imagine for a moment that the quantity you are interested in has a value in the range 42.0 - 42.999. Imagine further that you want as much precision as possible. As it stands, you are spending a chunk of your available bits representing the value 42, and that leaves fewer bits available to represent the 0.000 - 0.999, which in some sense is what you are ...


13

The example is making a fundamental mistake: it's using data as a primary key. It should create and use unique IDs. The comments debate how correct it is to assume that a zipcode maps to a particular street. Whether that's correct or not, the simple fact is that for this to work without a unique ID it must be correct, not only now but forever more. That is ...


12

Many tables already have a natural unique id. Do not add another unique id column (auto-increment or otherwise) onto these tables. Use the natural unique id instead. If you add another unique id, you essentially have a redundancy (duplication or dependency) in your data. This goes against the principles of normalization. One unique id is dependent on the ...


10

On larger systems, ID is consistency booster, do use it almost anywhere. In this context, individual primary keys are NOT recommended, they are expensive at the bottom line (read why). Every rule has an exception, so you might not need integer autoincrement ID on staging tables used for export/import and on similar one-way tables or temporary tables. You ...


8

is it fully normalised? no. are there problems with it? possibly yes. Just one example: "otherspelling" as a field in the word table breaks both normalisation AND is a problem. What if a word has multiple alternative spellings? And oh, you shouldn't aim for complete normalisation. Complete normalisation is itself a potential problem, especially for ...


8

You need two tables: Categories MuseumCategories They will look like this: Categories CategoryID (Primary Key) CategoryName MuseumCategories MuseumID (Foreign Key) CategoryID (Foreign Key) The MuseumCategories table connects each specified category to a museum. Each record in the MuseumCategories table connects one category to ...


8

It is not good practice to superfluous designs. I.e. - it is not good practice to always have an auto increment int primary key when one is not needed. Let's see an example where one is not needed. You have a table for articles–this has an int primary key id, and a varchar column named title. You also have a table full of article categories–id int ...


7

If the location is being represented by a zipcode (fine for the cases where most distance estimates are within a tolerance for the same zipcode), then I'd impose a CHECK constraint on the fields Zip1 and Zip2 that Zip1 =< Zip2. Of course this imposes a corresponding burden on the middleware logic to understand that the convention to assure only one ...


7

An auto-incremented (identity) primary key is a good idea except to note that it is meaningless outside of the context of the database and immediate clients of that database. For example, if you transfer and store some of the data in another database, then proceed to write different data to both database tables, the id's will diverge - i.e., data with an id ...


7

Or are there scenarios where you don't want to add such a field? Sure. First of all, there are databases that have no autoincrements (e.g., Oracle, which certainly is not one of the smallest contenders around). This should be a first indication that not everybody likes or needs them. More important, think about what the ID actually is - it is a primary ...


7

I usually use an "identity" column (auto-incremennting integer) when defining new tables for "long-lived" data (records I expect to insert once and keep around indefinitely even if they end up "logically deleted" by setting a bit field). There are a few situations I can think of when you don't want to use them, most of which boil down to scenarios where one ...


7

The problem with normalizing data is that you cannot be sure that you're changing it for the better, even if most cases you probably are. Also I'm assuming if you were to check for the existence of said address already in the database, you'd perform a case-insensitive check, so letter casing is not particularly important in that regard. The real problem ...


6

Does it mean that if we are using an ORM tool we do not need to design our database like before? You still need a design; it just takes a somewhat different form. The nature of the design shouldn't change all that much; you're still going to have records and relations, they'll just look a bit different. Does it mean that we do not even have to come up ...


6

As other people have made the case for an incrementing primary key I will make one for a GUID: It is guaranteed to be unique You can have one less trip to the database for data in your application. (For a types table for instance you can store the GUID in the application and use that to retrieve the record. If you use an identity you need to query the ...


6

Adding to the excelent answer by @john-wu another, another reason is when you have A BLOB type of column like a picture. You want to have that BLOB column in a separate table, not only for queries on the user table be quicker but also because you could move the table containing the blob to a different tablespace on cheaper, slower storage, keeping the most ...


4

Yes after normalization you still need indexing. The tables you are working with benefit just as much as the tables you had before normalization. In actuality, on their own they're just the same: tables. One thing you have to consider though, indexes help you find your way through your data faster. Normalizing a design of a database is always good, but ...


4

What you describe has nothing to do with normalization but with validation. You can simply have a source somewhere with the valid values. Then just validate before storing the information in your database.


4

I really don't think there is problem of VOs having an identity in database, as long as this identity remains hidden and transparent in domain layer itself. I would be data layer's responsibility to keep track of those identities so the domain doesn't have to care about it. But VOs still give you ability to denormalize. Denormalization can have it's ...


4

You would enter that fact by first creating (or looking up) a record in Date to represent Mar 2, 2013, then create a new record in Rental with three foreign keys. (You still need the table Rental, otherwise you can't represent arbitrary re-rentings of a customer or a car - or on the same date.) However, that is surely unnecessary. A date is one scalar piece ...


4

If you have 4 independant services, then they have to independant. No data should be shared in common DBs or similar, as then you simply make them dependant! Now while its OK to share a single DB in production, the services should use their own schemas as to DB is just there as a common container, similar to how a single Linux server can run all 4 services. ...


4

If you see someone putting different "things" into a single database table using a delimiter to keep them separate, that's a problem. It could be that someone does not have permission to change the database structure or that they just don't "get" databases. Either way, it's a performance and maintenance headache. In your situation I would probably create ...


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