I am relatively new to the database design or just database in general so I apologize first if any term or format I use is not super precise.
Scenario: Say I have a People table that contains PersonName, with other attributes say profession, as primary key and other non-primary attributes. And for whatever the reason I am only going to have two distinct people, Databaseguy1 and Databaseguy2, in the table. Normally I would save the PersonName as a char type with max size up to say 12.
So I am wondering that maybe in the People table I can have a PersonID as my primary key and represent it with bit (the smallest data type in SQL server, correct me if I am wrong) and create another table with PersonID(pk) and PersonName.
I believe that by doing so, I only need to record the person as 0 or 1 in the first table. It wont affect the overall number of rows but will save space in each row since now one of the fields is represented with bit data type rather than a variable char data type. And in the new table I am just going to have two rows, 0 to Databaseguy1 and 1 to Databaseguy2. Is this a valid argument or is there a better way to accomplish it? Will this affect the performance such as retrieving the data since now it has to go to a separate table to fetch the actual name of a person?
Other questions: will the relationship between the second table the People table be one to many relationship?
Best Answer
You would not actually save any space using BIT rahter than TINYINT (and would also give yourself more options to add other entities). This is because 8 or less bits are stored in a byte. If you were using multiple bit entries this could save you space, but it's ultimately very limiting. A TINYINT would use the same 1 byte of storage, but would allow you to store up to 256 different entity values.
Saying this though, from a practicality standpoint, unless you are looking at ridiculously high volumes of data the space savings are not going to be that significant regardless of using TINYINT or SMALLINT (~65k values & 2 bytes).
Using normalization for this sort of thing is very common, and helps prevent excessive bloat (after all in your second table you would store 1 bytes instead of twelve to represent the person entity). Don't forget to add foreign key references.