On Tue, 2001-10-30 at 13:10, Bennett Haselton wrote: > I'm creating a database where one of the tables stores data about news Web > sites, and I'm using the URL of the site as a primary key field. This > field value might change occasionally. I'm wondering if this is bad > practice, especially since foreign keys in other tables might point to the > news site table. MySQL doesn't enforce referential integrity, so if I > change the URL of a particular site, then another table in the database > might have a reference to a row that no longer exists. >
The latest version of the InnoDB handler enforces referential integrity. > So I assume the "right" way to do this would be to use an auto-incremented > number as the primary key field, and then have other tables refer to that > as their foreign key. My problem with this is that I like to be able to > dump the contents of the table and see something meaningful without having > to refer to other tables. Say I have an "articles" table in the database, > and one of the fields is a foreign key referring to the news site in the > "news sites" table where the article was found. If I dump the contents of > the "articles" table, all that I'll see is a number; then I have to go look > in the "news sites" table to find out which actual site that number > corresponds to. From this point of view, it's a lot easier just to use the > news site URL as the key field in the "news sites" table -- then that's > what can be used as the "foreign key" in the articles table. > There are other reasons to use an auto-increment field. For one, indexes on integers are faster and much more efficient than indexes on strings. Another alternative to auto-incremented fields would be to use a hash of the url as the primary key. Then you can effectively look up based on the url, while using a numerical index for speed and efficiency. That would not solve the foreign key problem though. > What would be ideal would be to use auto-incremented numeric fields as > primary key fields, and then have a special field in each table designated > as the "user-friendly field". That way, when you want to view the contents > of a table, the "table viewing" algorithm can take each field marked as a > foreign key, go to that table, look up the "user-friendly" string for that > row, and display that instead. This would satisfy the requirements in both > paragraphs above. > > Is there already a way to do this, and if not, which of the two options > above do people usually use? > > -Bennett > Generally people do not put "user-friendly fields" in tables. That's what joins are for :) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
