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?