1

I am developing a CRM application that stores some data on companies we work with. Such as for example: CEO name and physical address of their head office. I will often need to look up for companies located in particular city and then sort the search result by street name.

I know that proper solution will most likely be something like an address column of the integer type, which will point to the address table, which itself will contain other columns like state, city, street, housing_number, office_number all of which will itself be either an integers pointing to related tables (city, state, street) or the final piece of data (housing_number, office_number).

The problem is not just the fact that I'm much more comfortable working with something like one or two linked tables, rather than using difficult JOINs across 3, 4 (like the design i supposed earlier) or even more tables, but also that i don't really see the point of not doing something like this:

| id  |      name       |  ceo_name   |    city     |     street       | house  | office |
|-----|-----------------|-------------|-------------|------------------|--------|--------|
|  1  | Company Name 1  | CEO Name 1  | New-York    | 5th Ave          |    22  |     12 |
|  2  | Company Name 2  | CEO Name 2  | New-York    | 44th St.         |    42  |     88 |
|  3  | Company Name 3  | CEO Name 3  | Boston      | Irish Lane       |     2  |     14 |
|  4  | Company Name 4  | CEO Name 4  | Washington  | Tahoe boulevard  |    54  |     19 |

What problem can i ran into if i implement such a solution? I have all atomics so if the need rises i can always implement the 3-NF solution later on.

tnsaturday
  • 527
  • 2
  • 10
  • 31

2 Answers2

1

Listen to the first proposition of yours is the correct by the book solution but it does require feeling comfortable in SQL and relational databases, like everything in computer science it's all a question of efficiency, how big would the table be? Remember that in SQL the engine always draws all of the columns even if u emit some in your SELECT if you will have data types that are heavier (chars that take more memory bytes etc.) then obviously the table will become heavier and heavier. If your use will only be drawing the user by id (primary key) then your queries will never really be slow no matter how big they get.

It's all a question of scaling and what you're going to do with that data when constructing it like your first suggestion you plan for the future but you sacrifice time at the present.

Michael Ostrovsky
  • 629
  • 1
  • 7
  • 21
  • Well, it'll hardly be a few thousand rows in this database. More likely 1500 - 2000 rows with city and street being a utf8_general_ci VARCHAR(40) string. And i'm not very comfortable with SQL and the thing is now i'm prototyping it in PHP + MySQL just to see how things get going, but the final product will be in some framework and will use some kind of ORM like ActiveRecord. – tnsaturday Jan 18 '19 at 12:57
  • If the maximum size will be 1500-2000 and considering you will index the fields you will be drawing the data on then there's no problem doing what you suggested, it's just not future-proof that is all. – Michael Ostrovsky Jan 18 '19 at 12:58
1

Actual schema for the similar db application, maybe used as a boilerplate:

enter image description here

tnsaturday
  • 527
  • 2
  • 10
  • 31