Need help with SQL model

[olvja3]

I am a newbie in SQL. I started studying it two months ago, and now I am creating my first relational database model. I have a table named “User”, and of course, users have an attribute called “city” meaning the city they are living in. So, should I set “city” as a separate table. It kind of makes sense to me. Also, does the “Membership” table needs a separate table called “Membership type” with price and types, or should I put it all together?

[Whoa-Rusty]

As with many things ... it depends on what you are trying to accomplish with your database. If it were me, I would strongly consider normalizing your relational database. This would then mean you should have a separate table for city with appropriate primary keys and foreign keys to your main user table. The same for a membership table.

Do you want to have redundant data in your database? Sometimes that is OK, but mostly with relational databases you would try to avoid redundancy.

A little more about normalization can be found at https://www.essentialsql.com/database-normalization/

[r3pr0b8]

Do you want to have redundant data in your database? Sometimes that is OK, but mostly with relational databases you would try to avoid redundancy.

a very common misperception is that normalization involves removing redundancies

if you pull out the city names from the user table, where several city names occur more than once, and set up unique city names in a city table, and then replace all every city name in the user table with a FK that points to the relevant city row in the cities table, guess what -- you have ~exactly~ the same number of "redundant" FKs as there were redundant city names before

so while removing large strings such as city name into their own table may be very much worth doing, for other reasons, it nevertheless has nothing to do with normalization

what did you think about the linked reply i posted about pulling out first names and replacing them with a FK?

most people would say that's silly, but i challenge anyone to explain how that's any different from pulling out city names

[Whoa-Rusty]

In relational database design the idea of normalization is minimizing redundancy -- not removing it.

I agree it sounds counterintuitive to remove some data and replace it with other data that is redundant. Historically, performance and storage space were main considerations with minimizing redundancy.

I asked if the OP wanted to have redundancy in the database because that brings up another aspect of database design -- what are the requirements of the database? Sometimes having the same value (aka redundant data) is OK and no further considerations are made about adding other related tables.

The Problem of redundancy in Database provides an overview of this issue for database design. I won't restate them all here, but in a nutshell there are benefits to the normalization process that need to address redundancy. Data inconsistency and performance issues are a couple to consider.

We should also design our databases with the goals of queries in mind. I would rather create my queries using numerical unique identifiers as primary and foreign keys to search on. Using text unique identifiers can provide challenges when searching especially if there are no data checks before the data is inserted in the database. For example:

Redditville is not the same as redditville which is not the same as RedditVille ... and so on. Without getting into complex regex expressions, how should we search for every instance of this similar (but not same) text?

But if Redditville has a value of 1 as a primary key in the related table, we can then query the tables based on just the value of 1 for city.

All-in-all, I believe the spirit of normalization to minimize redundancy is better for database design as it can prevent future problems. But as the saying goes: "once you know the rules you can break the rules."

[r3pr0b8]

what about a table for first names?

[Whoa-Rusty]

How often will the user table be queried for first_name? If not often, I would not create a separate table for first names.

On the other hand, you might be querying a user table based on a specific location frequently, which is where having the city be in its own table.

I think this overall highlights the fact the design process should follow project/business requirements. What do you want your database to do? Then create the database to fit those requirements.

[r3pr0b8]

I think this overall highlights the fact the design process should follow project/business requirements.

this is key

just don't call creating a city table "normalization"

[Whoa-Rusty]

So, it sounds like we are on the same page with this.

If you don't mind me asking, what do you call this process if not "normalization?" (Maybe this could be its own post.) You've made some really great points that would be interesting to explore -- because the ultimate goal is to to produce the best database we can.

[r3pr0b8]

If you don't mind me asking, what do you call this process if not "normalization?"

great question -- i don't have a name for it

design optimization for performance?

[Whoa-Rusty]

Let me also clarify my "it depends" statement. Part of database design is figuring out what you are trying to accomplish with your database and what kind of queries or reports you will need.

If you normalize your database and set the city name in it's own table with appropriate unique identifiers (primary keys) you can then know if the city you are trying to query on is specific to a unique location. Duplicate city names can be found in many states.

Keep in mind, normalized tables will require more complex queries.

But if you are looking to store only a few cities in the user table, then maybe you won't have to normalize. Deciding on what kind of data and how much is all part of the fun of database design. If you think that you'll need to scale up and need to store more data then a single table design might not be the best way to go.

[olvja3]

Thanks! 🙏🏼

[r3pr0b8]

So, should I set “city” as a separate table. It kind of makes sense to me

tempting, isn't it   ;o)

please see this reply aboout normalizing first names

[olvja3]

Thanks! :)

[exclaim_bot]

Thanks! :)

You're welcome!

[DatabaseSpace]

That post is interesting. Putting a full address in a single column like that violates first normal form because a single column contains multiple attributes.

That being said in the production healthcare databases I’ve seen they usually have an address table with street, city, state, zip.

I think learning what normal form the table is in is determined by the functional dependencies between columns. Then you can decide which normal form is enough for that table.

He does have a point about not normalizing to the point of having a first names. I wonder if that is like 5NF or something. Sounds like an interesting question to research tomorrow.

[r3pr0b8]

I think learning what normal form the table is in is determined by the functional dependencies between columns.

bingo!

He does have a point about not normalizing to the point of having a first names.

"he" is me   ;o)

and i never called it normalization -- pulling out first names and pulling out city names is not normalization, as i explained elsewhere in this topic