Correl: Noobiewan: It is all about indexes. If some query can’t use any index it becomes terribly inefficient when the data grows big enough and it is exponential in best case

Vanslooten: Salle, but I see your point about the JSON type. My biggest issue here is about the user accounts. I’m not sure if it’s the best approach.

Correl: Noobiewan: There is no best approach in general. There is best approach for your needs and that translates to queries

Fiddler: Salle, let’s rephrase, I’m not sure if it’s a functional, practical approach

Hauss: Salle, could you give me your opinion?

Correl: Noobiewan: I can’t because I don’t know your queries

Quebedeaux: Salle, I have no queries yet.

Shorter: Salle, it’s all a plan

Correl: Noobiewan: Then you can’t design any database

Guin: Salle, by queries you mean actual SQL?

Correl: Noobiewan: You see design always starts with defining the purpose and usage. In case of databases that’s The Queries

Gangloff: Salle, do you mean actual sql?

Correl: Noobiewan: Imagine you are architect and I pay you to design some building, but I can’t tell you whether it will be family house, factory, sports hall, office building or something else. Then I insist that not only you provide me with best design, but you actually do all the construction works BEFORE I decide what it will be used for.

Correl: Noobiewan: Creating some database with some tables in it without clarifying what the queries will be is exactly the same

Schrock: Salle, how can I write SQL without having the database design? Wouldn’t it go together? Isnt designing the quries and the database part of the same?

Correl: Noobiewan: That’s what you did with your stackoverflow item: What is this id column in ***ets table for example? Are you ever going to use it in any query? I guess no

Correl: Noobiewan: You can try imagine what the actual SQL statements will be and it can help with the schema design. It often takes several iterations

Correl: Noobiewan: Another example: You say “If a user is removed or leaves . ***ets would need to be re***igned to another user .”

Tebbe: Salle, well the ***ets table is an example of an entity. The ID field is the unique ID that we need to update it, read it, etc

Correl: Noobiewan: Does that mean that whoever uses this database will or will not need to know about the history?

Askins: Salle, no history needed

Correl: Noobiewan: Depending on this requirement you can have very different designs

Correl: Noobiewan: Are you sure no history is needed? :

Correl: Noobiewan: Another one: “Another marginal case would be of an ***et that would belong to more than one user. I’m ignoring that case for now.”

Correl: Noobiewan: Ignoring marginal cases has huge negative impact not only on database design, but on any application development :

Correl: Noobiewan: The problem is you can not “ignore them for now” and hope you can easily modify something later to make “them” possible

Correl: Noobiewan: Ignore some marginal case now and later when someone insists that such marginal case must be allowed you will face deep refactoring of both your database and app code

Correl: Noobiewan: 1 ***et can belong to single user only and 2 ***et can belong to more than one user is the difference between one-to-many and many-to-many relationship and they are described with different structure

Correl: Noobiewan: For one-to-many two tables could be enough. For many-to-many you need reference table so three is the minimum

Martinat: Salle, I understand. I should have left that out, it was more like a thought. I will not implement that. The main issue I would like to tackle now is the Users x Agencies relationship

Correl: Noobiewan: It is simple: Start with defining the requirements :

Correl: Noobiewan: In case of databases that means define the output the user needs to get

Correl: Noobiewan: All sorts of reports and such. That gives good idea about queries you will need to create these outputs