hi,
i have a table which contains user information, (name, last name, etc) and address information, and have another table which contains place information and also address information, so I made a new table which contains addresses, and put the address_id inside of the user and places. I wanted to know if it's possible to delete the address when I delete the user/place, instead of it being the other way around (since the foreign field is in the user table) with cascading and foreign key constraints, without having to use trigger.
thanks

sorry, not possible

thanks,
so is this not something that is common? is the database set up the wrong way (i didn't put the foreign key in address since that would require me to have an extra field specifying whether it is a place address or a user address)? or is trigger the accepted way of doing this?

What would be the logical flow of deleting an row in the address table in this case. It seems like you are considering a 1-1 relationship between user and address and between place and address. It also seems like you are asking how to automate the deleting of the address row when the corresponding user or place row is deleted? Is this correct, or am I out in left field somewhere.

What happens in the case of multiple users at the same place? Or, if a user changes places(address)?

Basically, it's hard to automate the deletion of the parent record when a child record is deleted, because the parent record may have multiple child records. If it is always that case that a parent always has one and only one child record, I would argue that you should dispose of the relationship and keep all of the data in one table.

It's been a long day and I'm ready to go home, so if I am missing something obvious here, just let me know.

yeah you have it right,
place would be a restaurant for example, user is a person, so if a user changes address then the corresponding address would just be changed. the thing is, i think, address is not part of a user's information and an address can be repeated. the relationship is always one to one, and i see your point in that deleting one child might cause problems if there are multiple childs.
so i guess it isn't such a good idea to try to separate this information even if it's repeated in two separate tables?
thanks!

So, define your data a little better. A restaurant is a public place. Could a restaurant have more then one user? So, if one user from the restaurant changes addresses, other users would still need the restaurant's address, therefore it couldn't change. Therefore, you couldn't just change the address. You would have to associate the user with a different address.

The assumption I am working under is:
A user can have only one address.
An address can have more than one user.

I don't yet understand your concept of place. Does a place have just one address? Can an address have more than one place? For example. An office building that houses multiple companies could have one address, but would each company's office within that building be a different place?

i think you already partially hinted at the correct solution in the very first post

are you or are you not interested in an address regardless of whether there is anyone or anything in your application that has that address?

no, since you want to delete it

you do not want place-less addresses hanging around, and you do not want user-less addresses hanging around

therefore, address is not an entity of interest in your application domain -- address is, instead, an attribute of user, and an attribute of place

look, users have names, and places have names, but you hardly ever see anyone creating a "names" table in the same way that people are apparently so ready and willing and keen to create an "address" table

i wonder why that is...

Excellent explanation. Short, sweet and to the point.

well, the reason I did it is that an address contains several fields (where i live it'd be street, number, neighborhood, borough, city, state, latitude and longitude), but thanks for your help, i'm going to go ahead and have a different table for the place's address and for user's address (since, in the future a user might have more than one address), or maybe have the place's address directly on the place table
thanks