0

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.

enter image description here

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?

Improve this question
5
  • Is there a reason you can't add a record to the member's table from the input? Then you can have an ID for it and it simplifies your queries. If you can't do that, I recommend you have separate fields for a name vs. an ID. You can't enforce foreign key constraints when the column types are so different. If this is used in a lot of queries, then it doesn't help your index become more efficient either.
    – Berin Loritsch
    Jun 20, 2019 at 20:20
  • @BerinLoritsch I can't add temporary one-time only names to my members table, it is not possible in my project.
    – Dharman
    Jun 20, 2019 at 20:28
  • 2
    No problem, but I wouldn't have one column perform dual purposes. Have a separate column for the one off name, and keep the memberId column only for Member IDs. That's the least surprising approach, which will make it easier to maintain over time.
    – Berin Loritsch
    Jun 20, 2019 at 20:41
  • What about two or more people with the same name? Is that a concern?
    – GisMofx
    Jun 21, 2019 at 13:24
  • @GisMofx No, even if they exist in my table then they will have different IDs, so it is not a problem. Otherwise the field should be free to enter anything, even duplicate entries. Currently I have function which matches the duplicate names, so that should never be the problem anyways as it is forbidden in the design requirements.
    – Dharman
    Jun 21, 2019 at 13:32

1 Answer 1

Reset to default
5

Trying to create a single field that holds two types of data is not something I would recommend. It would make it impossible to have a foreign key to the members table.

I think the two column approach is the best way to go. I would consider using a view here that unions the join against the members table and the ad hoc names. That will remove the bloating of the SQL. You probably won't be able to update or insert against the view but I'm not sure that would even be desirable here.

Improve this answer
3
  • Would it make any sense to store the name from my members table in the char column as well as the ID in the other column? I suppose it would ease the select complexity, but it would hurt data integrity, right?
    – Dharman
    Jun 20, 2019 at 21:15
  • @Dharman: The ID is what determines your relational integrity. The char field is of no consequence. When you write your queries, you can decide whether you want the displayed name to come from your char column or from the Members table.
    – Robert Harvey
    Jun 20, 2019 at 21:31
  • 2
    @Dharman Such duplication of data goes against the core principles of relational data. I'm not dogmatic but but it does bring into question the use of a relational DB in the first place. You'll need to consider what it means when a member's name changes. Having of what it was at the point it was selected could be beneficial or problematic depending on your requirements.
    – JimmyJames
    Jun 20, 2019 at 21: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.