July 17, 2009 at 3:21 pm
Often times tables will contain common columns such as People and Events both have columns for location (latitude, longitude) or a photo (blob column). Is it best to leave these columns in each table or extract the common columns into a second table such as Locations, or Photos? When I do this I run into relationship issues. It becomes seemingly impossible to delete the primary table row (Location) when the foreign row (Event, Person) is deleted without messy triggers. Thanks.
July 20, 2009 at 6:20 am
erictyrrell (7/17/2009)
Often times tables will contain common columns such as People and Events both have columns for location (latitude, longitude) or a photo (blob column). Is it best to leave these columns in each table or extract the common columns into a second table such as Locations, or Photos? When I do this I run into relationship issues. It becomes seemingly impossible to delete the primary table row (Location) when the foreign row (Event, Person) is deleted without messy triggers. Thanks.
If you're normalizing your design for the database, then an entity like a location, or address, would be seperated from the entity of a person or event. For a location, I'd suggest, creating a location table, where you can store physical information about the location, such as it's street address and lat/long. Then, for each entity that needs to be associated with the address table, you create an interim table that allows you to assign multiple people or multiple events to one location. This allows for data reuse.
As far as deletes go, when building a robust system, you need to take them into account and build a process that deletes from the interim table, and then from the entity that was getting removed. For locations, you generally don't want to delete them. Most of the time (granted, not ALL of the time) an address doesn't change. Certainly a lat/long doesn't change. If you have a location, such as a convention center in your city, that center could be associated with 30 events in a year and up to 20,000 people per event, but the location data never changes, just the associations as events and people come & go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 20, 2009 at 11:59 am
I think it kind of depends on how the data is used.
I would think that if you were entering individual events and each even had its own location, then you might as well keep them in the event table. If it's something where you can select from preset locations, then you should definitely have it in a seperate table though. Also, if that's the case, then I would think that you would just have a foreign key in the event record to reference that location, so you wouldn't have to worry about removing it from the location table, so it could then be used again.
If it's just something where each event has its own location that isn't reused, but you end up storing it in a seperate table anyway (which like I said, I don't know if you need to), I don't think that triggers are necessarily a "messy" way of deleting the location or photo information. I love triggers because of that. I think another option if you're opposed to triggers would be to leave the location or photo data, and then have a scheduled process that removes any record that isn't attached to and event or person.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply