iOS normalisation/relationship question

[grandM]

I'm trying to save user data. I ran into this question. A town has got many streets. A street has a name. So a town has many streets. But has a street many towns? At first you would say the street only has one town. But that name will be present in many towns.

I could do the following:
A town has many streets
A street has one town
A street has a relationship to another Table called Streetname
That streetname would have a n to n relationship to street.

Your insights?

 

[dejo]

Unless there is some reason to store the street-names in a separate table (like you wanted to find all towns with a Main Street), I think you should consider the street-name an attribute of a street. Even then, a properly constructed query could get you this information. And then your database structure isn't over-complicated and looks logical.

 

[grandM]

Unless there is some reason to store the street-names in a separate table (like you wanted to find all towns with a Main Street), I think you should consider the street-name an attribute of a street. Even then, a properly constructed query could get you this information. And then your database structure isn't over-complicated and looks logical.

I had it as an attribute. Then I realized you could have the same street name over and over again. Alle the people living in that street would lead to the same street being stored. Moreover if that streetname was common in several towns it would be stored even more.
I was also running in problems with the nullify/cascading thing. It makes sense to have a one to one relationship between a town and a street. A street then can have one name but a name can belong to several towns. This way I can put a cascade between town and street. Between street and streetName I put a nullify.
[doublepost=1454354775][/doublepost]

Unless there is some reason to store the street-names in a separate table (like you wanted to find all towns with a Main Street), I think you should consider the street-name an attribute of a street. Even then, a properly constructed query could get you this information. And then your database structure isn't over-complicated and looks logical.

Damned, Core Data does not have a non duplicate option. Apparently I must check programmatically if the row does not exist yet?

I finally decided to reverse the changes. The approach was solid but as CoreData isn't checking for doubles this could get really really complicated. The extra space used and complications upon a street name getting changed or something like that don't seem worth the hassle.

 

[dejo]

A street then can have one name but a name can belong to several towns.

I think that's over-normalizing. Besides, if one town decides to rename a street, you've got a messy clean-up on your hands. Just think of the fact that multiple towns have the same named street as coincidence and nothing else.

 

[grandM]

I think that's over-normalizing. Besides, if one town decides to rename a street, you've got a messy clean-up on your hands. Just think of the fact that multiple towns have the same named street as coincidence and nothing else.

that was indeed one of my concerns
But I gathered I could drop the name of the street of a town 1 hence nullify it
As I nullify it would not influence the name in town 2
Upon adding a new name to the street in town 1 I suppose I would not run into problems?

 

[1458279]

One of the most important goals in normalizing a database is maintenance. You have to look at how the data is going to be used. Is it a simple data store similar to a note taking app, or is the data going to be edited and shared?

Look at the intended usage and how will it be edited, what happens when there's a street name change? Do you have to edit every record or just a simple look up table where you only change it in one place.

You mentioned "user data". If this is something like a record of places I've been, full normal might be over kill.

IMO, I'd focus on maintenance issues, because "user data" suggest a light maintenance need.

 

[dantastic]

The name of the street is not the only things the street 'is'. A Street has many other properties as well, such as number os parking spaces, fire hydrants and connecting streets. All these things make each Street unique, just like a real street.

So a Town will have many Streets. A Street will only have one Town. (Ignoring the real world example of streets connecting towns...).

Are you using Core Data? If you are you want to set the streetName property to indexed. It will allow fetches for tiwn name to return quicker. This way you can search for the Streets with a streetName of 'Main Street" and get several Streets with the same streetName back. - But they are of course unique streets and renaming one of them will not affect any other Streets you may have.

When modelling data like this you must really ask yourself if your objects are unique or of they are in fact shared.

An example of the opposite would be 2 brothers. They both have a playstation. But it's actually a shared device so in this case the playstation has a relationship to many owners. As you can see, this is different from your street example.

 

[Mascots]

When modelling data like this you must really ask yourself if your objects are unique or of they are in fact shared.

I often use the terms object and entity interchangeably, but I think that this is a situation where the term entity makes the most sense when referring to data models. The dictionary defines entity as: a thing with distinct and independent existence - just as this example, each street has it's own distinct and independent properties that determine it as a whole.

 

[grandM]

The name of the street is not the only things the street 'is'. A Street has many other properties as well, such as number os parking spaces, fire hydrants and connecting streets. All these things make each Street unique, just like a real street.

So a Town will have many Streets. A Street will only have one Town. (Ignoring the real world example of streets connecting towns...).

Are you using Core Data? If you are you want to set the streetName property to indexed. It will allow fetches for tiwn name to return quicker. This way you can search for the Streets with a streetName of 'Main Street" and get several Streets with the same streetName back. - But they are of course unique streets and renaming one of them will not affect any other Streets you may have.

When modelling data like this you must really ask yourself if your objects are unique or of they are in fact shared.

An example of the opposite would be 2 brothers. They both have a playstation. But it's actually a shared device so in this case the playstation has a relationship to many owners. As you can see, this is different from your street example.

So basically I ought to follow my initial idea being
A town has many streets
A street has one town (hence unique)
A street has a relationship to another Table called Streetname
A street can only have one name but a streetname can belong to many streets (in different towns).
BUT if a street were to change the name in one town this would change the name in all towns :s
I am using Core Data.

I reread your post and concluded you're setting streetname as an attribute to street. In that case the same name would reoccur for all residents. I thought this was against the concept of normalisation? If the street in a town changes its name all entries would have to be changed. Also data duplicates enlarging the database.
[doublepost=1454466717][/doublepost]

I often use the terms object and entity interchangeably, but I think that this is a situation where the term entity makes the most sense when referring to data models. The dictionary defines entity as: a thing with distinct and independent existence - just as this example, each street has it's own distinct and independent properties that determine it as a whole.

How does Core Data handle a cascade with a n to n relationship by the way?
Say you state the relationship "talked to" between Man and Woman
John talked to Susan and Donna
Bill talked to Tiffany and Donna
If I delete John will Donna still be available to Bill?

 

[dejo]

I reread your post and concluded you're setting streetname as an attribute to street. In that case the same name would reoccur for all residents.

Since this is the first you've mentioned of residents, and you haven't described how the resident entity relates to the other entities, how would we know about that?

 

[grandM]

Since this is the first you've mentioned of residents, and you haven't described how the resident entity relates to the other entities, how would we know about that?

English is not my native tongue
I'm fumbling around with CoreData
I just thought that for invoice software for instance the same street could reoccur a lot
That street would belong to a town
But basically I'm trying to figure out how one would deal with different situations

 

[1458279]

It might be helpful if you actually described the usage of this. Example is this for creating a work log sheet to hand into someone or is this a lookup table of streets, cities and states. How will it be updated is a big factor.

I've worked as a database designer and managed several SQL servers, it's very helpful if you have an idea of what a person is trying to do. Are you providing a table to people where you update it every month, are they going to update it, etc...

Also, some quick reading about data normalization can be helpful. I'm not trying to insult, it's just that some data storage solutions can have a quick and easy solution, like a vendor provided look up table that nobody else updates, like a rate table.

https://en.wikipedia.org/wiki/Database_normalization

 

[dantastic]

The street doesn't need to have a relationship with another 'name' table. Core Data will take care of that for you if you mark the 'streetName' property as indexed. You don't need to create a mapping table like you would in SQL.

As each street is unique if you change the name of one street it will not impact any other streets.

Actually, adding residents to the street is making the example much better. A resident can only live on a single street in a single town. So if each street existed in every town then each resident would also live in every town, and that's obviously not possible.

So in GooseTown there is a Street BeekStreet. It has many House (Entity), So there is a one to many relationship between Street and House. A House have a one to many relationship with Resident.

Because these are relationships if you change the name of BeekStreet to MainStreet the name of the street will change for all the Residents in all the Houses on the Street. So the streetName is not duplicated for all Residents - they share a relationship to the same unique object.

 

[grandM]

The street doesn't need to have a relationship with another 'name' table. Core Data will take care of that for you if you mark the 'streetName' property as indexed. You don't need to create a mapping table like you would in SQL.

As each street is unique if you change the name of one street it will not impact any other streets.

Actually, adding residents to the street is making the example much better. A resident can only live on a single street in a single town. So if each street existed in every town then each resident would also live in every town, and that's obviously not possible.

So in GooseTown there is a Street BeekStreet. It has many House (Entity), So there is a one to many relationship between Street and House. A House have a one to many relationship with Resident.

Because these are relationships if you change the name of BeekStreet to MainStreet the name of the street will change for all the Residents in all the Houses on the Street. So the streetName is not duplicated for all Residents - they share a relationship to the same unique object.

I get it. Basically if I wanted to create a separate table StreetName I would have to put a link to the town and the Street checking if the the street lies within that peculiar town upon changing its name.

Are you stating that indexing it Core Data will create this idea. Or would the street name still being saved for all those residents in your example?

 

[Mascots]

It seems to me that your approach to your model a little bit too board. In the case that you've given, StreetName is just a property to help define Street, and if it isn't, it should be: A Street entity itself is all that stands for the existence of a particular street - it's existence is only further detailed by the properties on it (name, length, oneWay, numberOfStreets, etc). Street names are only arbitrary information to a database, not equivalent to an identifier (like a human equating street names to identify it's particular existence in a limited world, like a town).

Stepping back, minus any additional detail, I see no reason to even normalize your database model to this level - It'd be faster and more efficient to enforce constrants to normalize data in setters prior to database insertion. This makes all of your interactions simpler, faster, and overall more concise.

Including interpolation & gaming, I can see very few exceptions in which such a normalized model would benefit.

 

[grandM]

It seems to me that your approach to your model a little bit too board. In the case that you've given, StreetName is just a property to help define Street, and if it isn't, it should be: A Street entity itself is all that stands for the existence of a particular street - it's existence is only further detailed by the properties on it (name, length, oneWay, numberOfStreets, etc). Street names are only arbitrary information to a database, not equivalent to an identifier (like a human equating street names to identify it's particular existence in a limited world, like a town).

Stepping back, minus any additional detail, I see no reason to even normalize your database model to this level - It'd be faster and more efficient to enforce constrants to normalize data in setters prior to database insertion. This makes all of your interactions simpler, faster, and overall more concise.

Including interpolation & gaming, I can see very few exceptions in which such a normalized model would benefit.

So not normalizing it to this level, would be faster? Codewise of course, but I mean for the database to react?

 

[Mascots]

The more normalized a database is, the more effort required to collect all information since it is split up. However, there are situations in which a heavily normalized will outperform a heavily denormalized one - there is no catch all and it depends on a myriad of factors oft controlled by the developer, conditions of project, and environment.

You should be looking for your sweet spot. Try not to over do it, but also remember to take advantage to what's at your disposal.

 

[dantastic]

I realize this question is just to better understand the topic but unfortunately the lesson is somewhat lost between the concepts.

What you are asking for is for 'Main Street' to only appear in the database once. We have all been answering a different question. To answer your question:

If you create a schema like so
Town - 1-n - Street - n-1 - StreetName

So a Town has many Street. A Street has a relationship with one StreetName. A StreetName can have a relationship with many Street. In this case the Street does not have a property streetName, it has a relationship streetName.

This means if you do a fetch for "name = Main Street" of the StreetName entity you will get only one result. If you change the name property of the StreetName Object then all streets previously known as Main Street will now have a new name.

If you want to change the name of only a single street you must fetch the Street object. When you have the Street object you must assign a new streetName object to the streetName relationship. This means your Street is now pointing to another StreetName. When doing this kind of stuff you need to be careful with the Core Data delete rules. (Hint - don't cascade!)


The reason why everyone have been answering a different question is you are somewhat looking to apply relational SQL techniques to Core Data. When using Core Data you would never do the above unless you had a good reason. And with the example given (Street name) you would never do this, you'd just make the name a property of the Street, make it indexed. You would not spare a single thought to the fact that you are going to duplicate 'Main Street' 100s of times because that is still going to be much cheaper than maintaining the above relationships.

Understanding and modelling data is a very large topic and it spans languages and databases. The big problem, why this is such a big topic always comes down to how the data is going to be used. You must design your schema from the point of how the data is going to be used as opposed to what would be the more elegant or correct representation. Otherwise you may find that your most common fetch contains horrible, nasty, really expensive inner joins.

 

[grandM]

I realize this question is just to better understand the topic but unfortunately the lesson is somewhat lost between the concepts.

What you are asking for is for 'Main Street' to only appear in the database once. We have all been answering a different question. To answer your question:

If you create a schema like so
Town - 1-n - Street - n-1 - StreetName

So a Town has many Street. A Street has a relationship with one StreetName. A StreetName can have a relationship with many Street. In this case the Street does not have a property streetName, it has a relationship streetName.

This means if you do a fetch for "name = Main Street" of the StreetName entity you will get only one result. If you change the name property of the StreetName Object then all streets previously known as Main Street will now have a new name.

If you want to change the name of only a single street you must fetch the Street object. When you have the Street object you must assign a new streetName object to the streetName relationship. This means your Street is now pointing to another StreetName. When doing this kind of stuff you need to be careful with the Core Data delete rules. (Hint - don't cascade!)


The reason why everyone have been answering a different question is you are somewhat looking to apply relational SQL techniques to Core Data. When using Core Data you would never do the above unless you had a good reason. And with the example given (Street name) you would never do this, you'd just make the name a property of the Street, make it indexed. You would not spare a single thought to the fact that you are going to duplicate 'Main Street' 100s of times because that is still going to be much cheaper than maintaining the above relationships.

Understanding and modelling data is a very large topic and it spans languages and databases. The big problem, why this is such a big topic always comes down to how the data is going to be used. You must design your schema from the point of how the data is going to be used as opposed to what would be the more elegant or correct representation. Otherwise you may find that your most common fetch contains horrible, nasty, really expensive inner joins.

Thank you. I did have a course at university about relational databases. This caused me to follow its ideas in Core Data. This example was a bridge where I hit difficulties. I realized that following relational databases would result in some nasty consequences.

I was also asking myself what Core Data does when you have a n to n relationship.
Say you state the relationship "talked to" between Man and Woman
John talked to Susan and Donna
Bill talked to Tiffany and Donna
If I delete John will Donna still be available to Bill?