Design: DB structure

[OrAbramovich]

We have to design the structure of our DB i.e. tables, fields, relations, indexes, keys, fields types, etc ...

[OrAbramovich]

First Revision

• Collections (tables): Apartment and User (there will be some additional embedded collections - as described in the fields).

• Apartment document will contain the following fields:

  • Id (integer)
  • Images paths (list of string)
  • Description (string)
  • Geolocation (Geolocation data)
  • Address - an embedded document containing the following fields:
    • City
    • Street
    • Number
  • "yes-no" features (parking, elevator, pets...) (list of strings). TBD - refernce id to a collection
  • Price (double)
  • owner_id (reference to the user's id of the owner)
  • Date published (date)
  • Num of rommates the publisher is looking for (integer)
  • Num of rommates currently in the apartment (integer)
  • Num of rooms (integer)
  • Floor:___ out of:___ (2 X integer)
    • Entrance date (Date)
  • Size (integer)
  • Comments (list of embedded document containing the comments' fields: id(integer), title (string), text (string), user id (reference to the user's id of the publisher), date, root comment for allowing tree structure (reference to the comment id - integer)).
  • Interested (list of references to the users' ids of the users who clicked on the "interested" button)

  Indexes: Price, features, geolocation, description

• User document will contain the following fields:

  • Id (integer)
  • First name (string)
  • Last name (string)
  • Birthday (Date)
  • Username (srting)
  • Password (string. we have to store an encrypted hash and not the plain one due to security reasons)
  • Email (string)
  • Profile picture path (string)
  • About (string)
  • Hobbies (list of strings)
  • Gender (Boolean)
  • Home city (String)
  • Roommates preferences (list of strings)

  Indexes: username, hobbies

[AdiOmari]

Nice work Or.

Apartment document:

• Shouldn't the image field be a list of images instead?
• What about the date of leaving the apartment (checkout date).

[OrAbramovich]

Added. Thanks.

[alonttal]

Hi @OrAbramovich ,

• I think the yes-no features ("tags") in the apartment entity should be a list of features instead of yes-no. For exmple if the apartment contains an air-conditioner but doesn't allows pets, then "air-conditioner" will be on the list and "pets" wont be.
• in Student, I think we should omit the field "Role", since a student can look for apartment one year and the next publish an apartment ad himself.

[OrAbramovich]

Hi @alonttal ,

• I just called it a "yes-no" features in order to be clear but, as written, it's a list of strings that are included in the apartment e.g "Elevator", "AC". We are not going to store strings/booleans about features that are not relevant to the apartment. We would like the list to be schema-free.
• Agree, the first design was to make a separation between student and an apartment owner - edited.

[OrAbramovich]

Second revision
(Following Back-end API discussion)

apartment = {
ID: ObjectID, //Mongo apartment ID - generated by MongoDB
ownerID: ObjectID, //Mongo user ID object
price: 1200,
entranceDate: Date,
interested: [ObjectID, ObjectID], //Mongo user ID object
address: {
city: 'city name',
street: 'street name',
number: 22,
apartmentNumber: 22
},
geolocation: {
lantitude: 22.07,
longtitude: 27.58
},
images: [path1, path2],
description: 'apartment description text',
tags: ['AC', 'Elevator', 'Pets'],
requiredNumOfRoommates: 2,
currentlyNumOfRoomates: 3,
numOfRooms: 2,
floor: 4,
totalFloors: 9,
area: 120,
ad: {
datePublsihed: Date,
comments: [
{
ownerID: ObjectID, //Mongo user ID object
date: Date,
text: 'some text here 1'
},
{
ownerID: ObjectID, //Mongo user ID object
date: Date,
text: 'some text here 2'
}
]
}
};

user = {
ID: ObjectID, //Mongo user ID - generated by MongoDB
firstName: 'name',
lastName: 'name',
birthdate: Date,
gender: 'male',
image: path,
about: 'some description here',
hobbies: ['Sport', 'Music'],
publishedApartments: [ObjectID, ObjectID], //Mongo apartment ID object
interestedApartments: [ObjectID, ObjectID], //Mongo apartment ID object
email: '[email protected]', //this will also be the username
password: 'my password', //should be encrypted
};

[alonttal]

DB was designed and coded.
future changes will be discussed in a separate issue.