CODEWITHSUNDEEP
database-design
Gary F
Gary F

Reputation: 69

Best way to store lot of data for quick request

I do have a database with millions of rows on a not so fast server, and I have to get some of these rows every week. The thing is that it's about people, and they are associated to a localisation number, but this number is different according to a distributor.

For exemple let's say we have Mr.X For distributor A, Mr.X localisation will be "01", but for distributor B, it might be "171".

So here is how my database look right now :

Table Person : Identifiant, name, ...

Table Distributor-A : Identifiant, localisation, ...

Table Distributor-B : Identifiant, localisation, ...

There is and will only be 2 distributor, so I thought about putting all the information I need every week as "name" from table Person also in the tables Distributor-[], so I won't have to make an inner join on the table Person which is really heavy and slows a lot my querys.

But I also know it's usually a bad idea to store the same information at several places in a database. However I'm not used to work with important amont of data and I really need to make it faster, so that's why I need some advice !

Thank you for your time

Upvotes: 0

Views: 119

Answers (1)

Amir
Amir

Reputation: 721

Heyy Gary, pardon me if i didn't understand your question correctly.

So what i got is that you have many persons and each person has localisation numbers different for both distributors.

Lets say , Person named "John Doe" has his personal information stored in Persons table.

John has relationships with both distributors, so to get the localisation number from both distributors table named Distributor_A & Distributor_B we need to query both distributor tables, well whats an easy way of doing it?

Let see if this helps you out.

Persons table design

Columns
-- ---- --------- ----- --- 
Id Name Telephone Email Age Etc...
-- ---- --------- ----- ---

We are going to make a new table named "Localisation_numbers".

Localisation_numbers table design

Columns
-- --------- ---- ----
Id Person_id No_a No_b
-- --------- ---- ----

We are going to store localisation numbers in table Distributor_A , Distributor_B & also in Localisation_numbers.

OR

You can store localisation numbers only in Localisation_numbers table & drop table Distributor_A & Distributor_B.

You can set No_a & No_b column default value to 0 which would mean that the person does not have relationship with the distributor and if the value is not 0 that means they do have a relationship with distributor.

To pick John Doe's localisation numbers , check the example below.

Persons table
-- ---- --------- ----- --- 
Id Name Telephone Email Age
-- ---- --------- ----- ---
12 John 010101010 john@ 39

&

Localisation_numbers table
-- --------- ---- ----
Id Person_id No_a No_b
-- --------- ---- ----
1  12        348  210

Now we need to search for the row in Localisation_numbers table which has Person_id same as John's Id in Persons table & then we can pick values of column No_a & No_b.

Let me know if this helps you and if you understand this. Thanks.

Upvotes: 1

Related Questions