I need to do a database modeling for the following scenario:
A Task (which is a ticket, a service request) will always have a Address , which is where this task will be serviced. The Task can be opened for service on a Property (which would be a building for example) that has an address known and previously registered in the system, or it can be opened for service at any address, which will be selected at the time the task is opened.
In cases where the task is opened for care in a property, it is necessary to extract in a report later the attendances related to this property.
My question is how to make this "optional relationship" of the task to the property in a correct way. Home I initially thought of a modeling where the linking of the Task with the Address is done through a table called TaskEndereco, this table would have a foreign key to Property and a foreign key to Address. Home In cases where the task was opened for a Property, I would associate the property with the TaskTable table and take the address of the property. Home In cases where the task was opened for any address I would associate the Address in the TaskTable and would have the address directly.
Could you suggest better ways to model this specific Task scenario for your Address being optional the Property.