[SalesForce] Best practices for designing a Custom Object. Experts please adivce

First define a unique external Id on Contact. This will be a value you define such as student id or some other unique identifying information. If you don't have that, then you could use a concatenation of first name, last name, and email address. When you upload the Student info into contacts, also load the external Id with whatever you've defined as the unique Id for each student.

On the custom object, you want a lookup field to contact. Prepare your grade file so it has the same unique Id for each row. It can be repeated multiple times. You'll then do a second load with the grade information. In dataloader.io, you can define what external Id you want to use to fill the lookup field. Note that you need to perform an Upsert operation to be able to use the External Id.

I can't offer you any insight into why you cannot do this, you just can't unfortunately, it's a seemingly arbitrary limitation of the platform and the Product2 object. Unless someone who knows about the internals of the platform comes across this I doubt you'll ever get a definitive answer.

In terms of working around it though, the only way to achieve this is to create your lookup as a standard non-required Lookup and then enforce that the value of this lookup cannot be blank using a validation rule.

Your validation rule will look something like this:

ISBLANK(ProductId__c)

If the Product is deleted then this will result in the value of the field being cleared, as per the default 'Clear the value of this field.' behaviour, which will prevent you updating the child record until a valid lookup ID is entered.

If you want to change this behaviour you'll have to write a before delete trigger on Product2 with your desired logic.