1

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?

2
  • 1
    "So I am wondering that maybe in the People table I can have a PersonID as my primary key and represent it with bit [...] and create another table with PersonID(pk) and PersonName." @user3444882 are you saying that there would be two tables to represent a person? – Joe Borysko Nov 29 '16 at 18:47
  • @JoeBorysko yes but the idea is that I can reduce the overall size by taking this design approach. I guess the second table does not represent a person but rather it is like a table you can look up to for that person's name. – user3444882 Nov 29 '16 at 18:53
1

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.

2
  • First all thank you. Is it true that the second table will only have two rows assuming there are only two people. And the foreign key will be the PersonID right? srry to bombard you with questions. – user3444882 Nov 29 '16 at 19:03
  • The PersonID would be the PK in one, and FK in the other. Just re-reading your question. So if you have PersonName table with PersonID, PersonFullName columns you'd have the PersonAttribute table with an AttributeID PK, and PersonID with FK to PersonName.PersonID. – Nic Nov 29 '16 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.