I would like to know the best way to implement tracking whether a pet Cat is a kitten or a adult cat. I have a PETRECORD table that store basic info about a pet. I have a CATINFO Table that stores basic info about a cat. I want to keep track of whether or not the cat is a kitten or an adult cat. I am unsure whether to create a filed in one of theses tables, or create another table with the PETRECORD_ID and CATRECORD_ID and a DESCRIPTION field with cat or kitten being the data in the row. Is this the proper way to normalize my data? My tables would be thus: TBL_CATINFO CATINFO_ID int PK NAME varchar(50) BREED varchar(50) TBL_PETRECORD PETRECORD_ID int PK CATINFO_ID int FK //some other info I collect about the cat TBL_CATTYPE CATTYPE_ID int PK DESCRIPTION varchar (10)//Kitten or CAT This is where I'm unsure of how to proceed. Should I put Kitten, and AdultCat in this table?? I am sorry if this is simple, but I am learning still. Any help in pointing me in the right direction would be great.
Neither. A kitten will become a cat but the data in your table won't change at such a time. You need to store only the birth date of the animal and have your code decide if it's a Kitten or a Cat on the fly.