I am not sure how to explain my problem, but basically I have an input field which lets you enter member's name or select one from a dropdown list.
If the user has selected the person from the list then I would like to store in database the ID which would correspond to memberId
from my table members
. However, if the name entered in the field is not an existing member then I would like to store that as a free-text value in the DB instead of the memberId.
What I have tried so far is to have a single column varchar(255)
which would either store an ID or the name as it was entered in the field. Then I would query it with a join and a cast.
SELECT * FROM myTable LEFT JOIN members ON CAST(myTable.member AS CHAR) = members.memberId;
I know that this is not the best solution, but my other alternative is to have 2 separate columns which would also make all my SQL queries complex and bloat the table (2 columns to store the same entity).
Is there a better alternative?