0
I need to do a database modeling for the following scenario:
One Task(which is a ticket, a service request) will always have a Address, which is where this task will be attended to. To Task may be opened for assistance on a Immovable(which would be a building for example) that has a known address and previously registered in the system, or it can be opened for service at any address, which will be selected at the time of opening the task.
In cases where the task is opened for service in a property, it is necessary to extract in a report later the attendances related to this property.
My doubt is how to make this task "optional relationship" to the property in a correct way.
I thought initially in a modeling where the connection of the Task with the Address is made through a table called Taskforce, this table would have a foreign key for Immoble and a foreign key for Address.
In cases where the task was opened to a Property I would associate the property in the table Tarefaendereco and take the address of the property.
In cases where the task was opened to any address I would associate the Address in the table Tarefaendereco and have the address directly.
Could suggest me better ways to model this specific scenario of Task for your Address being optional the Property.
What would be the difference between property and address? To me it seemed the same thing. The detail that changes is that the property has a name, which could easily be a column nullable database. And if the task/address ratio is 1 to 1, why two tables? You can store everything in the same.
– Woss
@Andersoncarloswoss The property in terms of table would be basically a name same, but it is as a separate entity because there would be a register of property where it would put this name and its address. <br> The task/address relation would be 1 to 1 even, the idea of isolating in the table Tarefaendereco was due to be able to connect with Address directly or with Property.
– hebertrfreitas
Then I would make two tables only: the Task, with an "idEndereco" column, as a foreign key to Address, and the Address table, which includes the "name" column of the optional property.
– Woss
@Andersoncarloswoss I understand, your idea would be more simplified but functional. If I had for example the need to store an additional detail of the service address for that task as for example room, sector, where the task will be served, it would be justification to create the table Taskforce for example. I forgot to mention this possibility in the initial description of the problem.
– hebertrfreitas