locked
Database Modeling For GeoLocation Networking App RRS feed

  • Question

  • Hello,

    I am trying to create a Geo-Location networking mobile app DATA MODEL. I am not sure if i have covered all so wanted to get advice from experts who are data modelers......

    I am evovling in identifying the entities and its attributes. I would need some help from you guys on how the 
    1) linkedIn account can be injected in Users table 
    2) what if User register using email, 
    3) how do we get the messages linked between users. (confused here)
    4) I think the we need to store the current location to the Users table to lat and long fields so it is instant retrieval and history to Location_history table.

    Any Help would be of great use....

    1. User (Master table)
    2. Connection
    3. Message
    4. Skill (Master table)
    5. Occupation (Reference to User)
    6. UserSkill (Reference Skill and User)
    7. Education (Reference to User)

    Users can either logon using LinkedIn account or register using email and password. If they Register using their email address then they would need to activate the account thats been sent to their email address, so an attribute to hold active or not needs to be in the User Entity.
    What would be the password field does it need to varchar and to have hash values... huh too many to think about now
    Users
    ------------------------------------------------
    user_account_ID AUTONUMBER PK
    Date_Joined Date 
    DOB Date
    Email_Address Varchar
    Password
    LinkedInID Varchar
    First_Name Varchar
    Middle_Name Varchar
    Last_Name Varchar
    Gender Varchar(1)
    Other_Details Varchar
    ProfileImage
    Geo-Lat Float
    Geo-Long Float
    isLive bit

    Location_History
    ------------------------------------------------
    user_account_ID Number Composite PK Fk (Users)
    start_date Datetime Composite PK
    end_date Datetime
    Geo-Lat Float
    Geo-Long Float

    Skills
    ------------------------------------------------
    skill_ID Numeric PK
    skillName Varchar
    description Varchar
    --isLive bit


    UserSkill
    ------------------------------------------------------------------
    user_account_ID Numeric Composite PK, FK (Users)
    skill_ID Numeric Composite PK, FK (Skills)

    Occupation/Experience
    --------------------------------------------------------------------
    user_account_ID Numeric ---- Composite PK FK (Users)
    start_date date ---- Composite PK
    end_date date ---- Composite PK
    job_title Varchar
    company_name Varchar
    is_current_job Bit
    is_freelance_work Bit
    job_location_city Varchar
    job_location_state Varchar
    job_location_country Varchar
    description Varchar

    Education
    ----------------------------------------------------------------------
    user_account_ID Numeric ---- Composite PK FK (Users)
    degree_name Varchar ---- Composite PK FK
    major Varchar
    institute_university_name Varchar
    from_date date
    to_date date


    Questions
    ------------------------------------------------
    Q : Regarding occupation, do we need of city, state or country, i dont see city, state or country in the prototype
    A : No

    Q : Regarding Education, do we need of city, state or country, i dont see city, state or country in the prototype
    A : No

    Q : Do we need to store the histroy of location of users been too
    A : Yes, and the history of location will be done by the front end app (Java micro services, React native for cross platform app development)

    Q : do we need to have report abuse about user?
    A : Yes

    Q : Searching
    A : For searching capability we might have to have a de-normalized table and map it to aws elastic search. But we can discuss that later. We want the search functionality to be fast.
    PLEASE NOTE THIS NEEDS TO DISCUSSED MORE

    Q : but while storing the skills to skills table can we segregate them like science or maths or IT but just allow users to select the skills alone?
    A : Yes
    So FieldMaster and Skill Master tables required.....

    Thanks

    Senthil

    Friday, August 3, 2018 8:27 PM

Answers

  • Hi SenthilP Nathan,

    According to your description, your problem is more than a database problem, it requires considering many other technology, I can only provide some suggestion from database end.

    >> linkedIn account can be injected in Users table

    We can create a table which stores these login information, we can use user id as primary key. Linkin should provide an API to handle this. We can use a varchar(max) column to store the information about Linkin.

    >> If they Register using their email address then they would need to activate the account thats been sent to their email address, so an attribute to hold active or not needs to be in the User Entity.

    We can create a column in the login information table, which stores the state of this account. 

    >> What would be the password field does it need to varchar and to have hash values... huh too many to think about now

    It requires varchar to store hash values.

    >> how do we get the messages linked between users. (confused here)

    I can't understand this problem very well, do you mean finding the user near another user? We can use the location information and then calculate the distance between them.

    >> I think the we need to store the current location to the Users table to lat and long fields so it is instant retrieval and history to Location_history table.

    I think storing this information as JSON and then store the JSON in SQL Server is more suitable.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 6, 2018 9:40 AM

All replies

  • Any Help is much appreciated....
    Sunday, August 5, 2018 11:40 AM
  • It  is unclear that you are asking the question and answering at the same time.....

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, August 5, 2018 11:45 AM
  • Hi SenthilP Nathan,

    According to your description, your problem is more than a database problem, it requires considering many other technology, I can only provide some suggestion from database end.

    >> linkedIn account can be injected in Users table

    We can create a table which stores these login information, we can use user id as primary key. Linkin should provide an API to handle this. We can use a varchar(max) column to store the information about Linkin.

    >> If they Register using their email address then they would need to activate the account thats been sent to their email address, so an attribute to hold active or not needs to be in the User Entity.

    We can create a column in the login information table, which stores the state of this account. 

    >> What would be the password field does it need to varchar and to have hash values... huh too many to think about now

    It requires varchar to store hash values.

    >> how do we get the messages linked between users. (confused here)

    I can't understand this problem very well, do you mean finding the user near another user? We can use the location information and then calculate the distance between them.

    >> I think the we need to store the current location to the Users table to lat and long fields so it is instant retrieval and history to Location_history table.

    I think storing this information as JSON and then store the JSON in SQL Server is more suitable.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 6, 2018 9:40 AM
  • Hi Uri,

    please consider them as questions, but i am a midway trying to create just a data model and wouldnt require anything from the front point of view, Would like to create data for geolcation networking app.

    Regards

    Senthil

    Monday, August 6, 2018 4:17 PM
  • Teige,

    I am trying to create a suitable data model and regarding the app design an code it will front end teams job.

    >> how do we get the messages linked between users. (confused here)

    Sorry was not clear about the above sentence. Tables required for messaging, something like a messaging app excluding groups and group messaging.

    Is there link for this kind of database model so I can look into it to create scalable database.

    Thanks

    Senthil

    Monday, August 6, 2018 4:23 PM
  • Any more suggestions for SenthilP?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, February 11, 2019 8:40 PM