0

I have to create a booking software and I've started to design the database. The room may be in the any place of the world so I would like to have advice for address. Reading a lot of similary question I have designed this schema, what do you think about? Is it enough to store all type of addresses?
I followed this structure.

enter image description here

luca
  • 3,248
  • 10
  • 66
  • 145
  • Why is `FloorNumber` part of `Building`? And for LAT/LONG I’d rather use DECIMAL, to avoid issues with FLOAT inherent imprecision. – CBroe Jun 14 '17 at 11:37
  • `City VARCHAR(45)` - http://www.fun-with-words.com/longest_place_names.html – CBroe Jun 14 '17 at 11:38
  • I would consider making City an attribute of Address rather than a separate table. Let users fill this detail in themselves rather than trying to generate a list of every possible city in every country by yourself... – Neil Hibbert Jun 14 '17 at 11:41
  • I update city with VARCHAR(255) and I create city and country as lookup table. FloorNumber containd the number of floors for each building. About LAT/LONG I read about FLOAT(10,6) like google maps, but I read also about geometry. At the momet I updated in according to https://stackoverflow.com/a/12504340/4004025 – luca Jun 14 '17 at 12:04
  • Can't judge a schema without knowing what `SELECTs` will be performed. Please sketch them out. – Rick James Jun 28 '17 at 13:26
  • Do not just `FLOAT(m,n)`; it leads to an extra rounding. See http://mysql.rjweb.org/doc.php/latlng#representation_choices for deciding on the 'best' representation for lat/long. – Rick James Jun 28 '17 at 13:28
  • @CBroe - 92 characters in this Russian town; don't know how long in Cyrillic: 'Poselok Uchebnogo Khozyaystva Srednego Professionalno-Tekhnicheskoye Uchilishche Nomer Odin'. Lat/long: 53.8897/40.6116. – Rick James Jun 28 '17 at 13:36

1 Answers1

1

  • Don't use a 4-byte INT for "country_id", use the standard 2-char "country codes"; it is smaller and avoids a JOIN.

  • There is not really much need to have "city" split out from address -- it does not save enough space to matter, nor is it is useful for "normalization".

So, combine Country and City into Address.

  • It is rarely useful to have 1:1 relationships; it is almost always better to merge the two tables into one. I am thinking about Building and Address.

  • Might you have a billion "floors"? Don't use 4-byte INT when 1-byte TINYINT UNSIGNED is more appropriate. See also SMALLINT UNSIGNED (2 bytes, range of 0..65535).

  • DECIMAL(6,4)/(7,4) is all that is needed for lat/long. 8 decimal places is getting into microscopic distances.

  • Pick the appropriate CHARACTER SET. country_code and (I suspect) postal_code can be ascii anywhere in the world. (I could be wrong -- Bangladesh uses non-Arabic numerals on license plates.) Other VARCHARs should probably be utf8mb4 if you are truly international.

It is good that you read a lot of similar questions to come up with the schema. It is unfortunate that they were novices.

Rick James
  • 135,179
  • 13
  • 127
  • 222