Обсуждение: database design and refactoring
Hi all, in my database I've got a table with a key that is char string, since it was supposed to support values like strings. Running the database, the users decided to place numbers as strings, so values like 00110002 and so on. Now I was wondering to refactor my database and change the char field into a numeric one but here comes problems: the field is referenced by other tables and views. I guess there's not, but you'll never know until you try: is there a tool or a way to easily do such refactoring or should I write a program on my own to do this? And moreover a database design question: is a better idea to choose always (when possible) numeric keys? I'm thinking about the database portability, since not all databases support sequences (that can be used with a concat to provide an automatic string key). Thanks, Luca
On Jan 3, 2007, at 5:24 , Luca Ferrari wrote: > Running the database, the users > decided to place numbers as strings, so values like 00110002 and so > on. Note that '00110002' is not a number (i.e., it's not equal to 110002): it's a string of digits. > is there a tool or a > way to easily do such refactoring or should I write a program on my > own to do > this? Not that I know of, though ALTER TABLE <table> ALTER <column> TYPE ... is pretty easy to use. > And moreover a database design question: is a better idea to choose > always > (when possible) numeric keys? Depends on your requirements. This is an oft-discussed topic about which you can find many more opinions by googling on natural and surrogate keys. Michael Glaesemann grzm seespotcode net
Michael Glaesemann wrote: > > On Jan 3, 2007, at 5:24 , Luca Ferrari wrote: >> And moreover a database design question: is a better idea to choose >> always >> (when possible) numeric keys? > > Depends on your requirements. This is an oft-discussed topic about > which you can find many more opinions by googling on natural and > surrogate keys. Come on, it's been a whole, what?, three or four weeks since the last natural v. surrogate debate? Seriously though, Luca, for information on that vein, peruse the archives or do as Michael suggested and Google. -- erik jones <erik@myemma.com> software development emma(r)