asked on Help with my Model Class for API and database tables creation
Hello EE,
I am creating a ASP.NET Core API project and it's so far its going well. I got my Model classes, my controllers and I am able to migrate and update-database so that it created my tables in SQL Azure database.
My question is, I am having a hard time to properly make it work when the time comes that I have some relationships (foreign keys)
Example I want to create those Apis / Tables for :
I want a Customer table (ID, firstname, lastname)
I want a Profession table (ID, customerID, location, title) and 1 customer could have many profession.
I want a ProfessionFavorites Table (ID, customerID, professionID) .. so 1 customer could many favorites
My question is, i dont know how to write those in my Model class so that all foreign keys properly make its way to the database,
Could you help me write the Model class for all three ? Also, the next goal is to Scaffold all this to create the controller for API
For the Favorite section, im trying to say that 1 customer could have many favorites
so im trying to say that: Customer ID 1 could put in favorites Profession ID 100, 101 and 102 and 103
so table could look like :
ID custID professID
1 1 101
2 1 102
3 1 103
Also last question.. in order for the database to do all the proper foreign keys, i ahve nothin else to do? the management console by typing update-database should do all this, am i correct ?
I remember I think i had an issue with the "ProfessionFavorite" i was having an error of 'cannot cascade delete' blabla
in general yes, I just would design it a little bit different...
Customer (Core data)
- CustomerID
- Customer Name
...
...
Profession (Core data)
- ProfessionID
- Profession Name
...
...
Customer Profession (Assingment data)
- ID
- Customer ID
- Profession ID
Customer Profession Favorite (Assingment data)
- ID
- Customer ID
- Profession ID
and as you can see, the Profession and Favorites tables are the same, you could also define just one table
Customer Profession (Assingment data)
- ID
- Customer ID
- Profession ID
- IsFavorite (yes / no)
A cascade delete is not necessary as you do not want to delete a cutomer or profession, if you delete an assignement.
The foreign key relation ist Customer ID to Cutomer ID and ProfessionID to ProfessionID, where the assignement tables are the N side and the core tables the 1 side.
https://www.experts-exchange.com/questions/29233778/Problem-with-my-API.html
not quite sure if I hit the point...
A foreign key contraint inside the database is just a constaint, so you can not write a record, if the dependent value is not provided.
To put the correct relation into your database, you have to take care yourself. So you have to find out the related IDs for the foreign records and then save them into the according table.
So several records in the profession table which the same Customer ID
And several records in the ProfessionFavorites table with the corresponding CustomerID and professionID.
From the construction I would interpret profession as a property of the customer.
So you have a customer table with customerID
You have a profession table with a professionID (definition of profession)
You have a customer_profession table, which contain the CutomerID and professionID to put them together.
With the favorites you can either extend the customer_profession table with a boolean tag to declare an existing profession as favorite... (means a few of existing professions are favorites)
or, if there are favorites, where you do not have a profession for...
another customer_favorites table, which contain the CutomerID and professionID