Table Relationships vs Choices - Which to use

[Trife]

I come from a SQL background and when I need to have a list of items (ex. Order Statuses) I would have a Status table with a FK relationship.  Dataverse has a column type Choice that I can fill with a list of options.  What is the best practice on using either approach in Dataverse?  Can I add records via code and select the option in that choice easily?  All the records in my table would be created via code and not from a PowerApp so understanding this would be beneficial.

Thanks,

[v-xiaochen-msft]

Hi @Trife ,

Could you tell me:

Do you want to associate two tables in Dataverse?

If my assumption is correct, you could try the lookup data type.

It is similar to the foreign key.

In addition, the choice column sometimes has usage restrictions.

I think this link will help you a lot : Here

Best Regards,

Wearsky

If my post helps, then please consider Accept it as the solution to help others. Thanks.

[Trife]

Hi @v-xiaochen-msft ,

What are the usage restrictions for the Choice column?
I am trying to determine which option is best.  If I created a 2nd table in Dataverse for the status it would have StatusId, Status Names as columns.  I am assuming the Choice column has been introduced to simplify the overall table architecture. 

[Drrickryp · Accepted solution]

@Trife 

IMHO if your choices are static, ie you won’t change them from inside the app, it’s ok to use a Choice column. If you will add delete or change them, make them a separate table that can be edited as needed.

[v-xiaochen-msft]

Hi @Trife ,

The choice column is used to set several options. Users can choose their own value based on these options.

Choice column is suitable for operation on a single table. (Usually used like this. )

Look up column is used to associate two tables.

You could find a corresponding record in table B from a record in table A.

Best Regards,

Wearsky

If my post helps, then please consider Accept it as the solution to help others. Thanks.

[Trife]

Thanks.  Looks like I will go with the Choice column.  

[Paulash]

Depending on your type of design, choice is most commonly used when it involves a single table, and the choices are static. but dealing with complex and multiple tables, which is dynamic such as directional choices, Relationships will best fit. 

[Drrickryp]

@Paulash 

I never use choice columns, if a single column table is used, I just hard code the choices in PowerApps

["choice1","choice2","choce3"...etc], otherwise, I create another table for the choices and use the ID of that table as a number column in the original table.