Best Practices: Creating a simple CRUD web application with users/roles

consider having a completely separate table just for wines that is not associated with users.

this table has wine parents and children. the parent contains fields for the winery, the name of the wine, location, etc. the children would be the specific years of the vintage.

so a user enters a wine name in his tracker. the system looks it up - if its not found then the user creates the record. system creates a parent record and a separate child record with the year.

if the wine is found - then the user can update the parent record, and either chose a year, or enter a new one.

system would then add a record to a separate table - users_wines - with the id of the parent (the wine) and the id of the child (the specific year)

this opens up many different possibilities including having wine lists that are shared by groups of users.

you also can easily change the relationship of the user and his wine bottle - because they are separate. the user can change status on users_wines - wine in cellar, drinking the wine, empty wine bottle - and this does not affect the record for the wine itself.

====Edit - yeah mentioning the wine.com api would have been good info. that gets into much larger questions. suggest that you first define what your users are able to do. My first question is - what if a user does not like the name coming back from wine.com? and what if there is an actual mistake in the api? are you going to customer service a wrong wine name coming back from the api?

Or - Can a user rename a wine in their list? if they can then the data relationship with wine.com is going to be very different.

personally i would think it would be annoying to try and create a wine list, but you cant edit the names. it seems like thats removing too much control from the user.

however if this is an app for restaurants that need accountability - then having the wine name be unchangeable could be considered a feature.

so spend some time with your user roles and user stories. then let that inform the data relationships.