Alpha Video Training
Results 1 to 28 of 28

Thread: ERD Diagram for Property Management

  1. #1
    Member
    Real Name
    Rick Chen
    Join Date
    Aug 2011
    Posts
    18

    Default ER Diagram for Property Management

    Please help me verify my attached ER diagram.

    I am building a Work Order module and so far have defined the following entities:

    1) USERS – Each USER can access one or more COMPANIES
    2) COMPANY - Each COMPANY can have one or more PROPERTIES
    3) PROPERTY – Each PROPERTY has multiple apartment UNITS
    4) UNIT – Each apartment UNIT can have multiple rental APPLICATIONS
    5) APPLICATION - Each rental APPLICATION can results in 1 LEASE
    6) LEASE – Each LEASE have 1 TENANT (occupants listed separately)
    7) TENANT – Each TENANT can have multiple TICKETS to report problems
    8) TICKET – Each problem TICKET can have multiple WORKORDER
    9) WORKORDER – Signed off upon repair job completion

    I have defined a 1:n Identifying Relationship for all entities except LEASE and TENANT (1:1 Identifying Relationship), this resulted in a 7 levels deep cascading reference for the last entity WORKORDER. So by the time I got to the last table WORKORDER, it wound up with the following PKs & FK. With the exception of the very 1st key, everything else is generated by MySQL Workbench:

    Primary Keys Fields (all are INT, Auto increment)
    -------------------------------------------------

    ID (WORKORDER_ID) - The only column I specified
    TICKET_ID
    TICKET_TENANT_ID
    TICKET_TENANT_LEASE_ID
    TICKET_TENANT_LEASE_APPLICATION_ID
    TICKET_TENANT_LEASE_APPLICATION_UNIT_ID
    TICKET_TENANT_LEASE_APPLICATION_UNIT_PROPERTY_ID
    TICKET_TENANT_LEASE_APPLICATION_UNIT_PROPERY_COMPANY_ID

    Foreign Key
    -----------

    WORKORDER_TICKET

    This seems normalized to me but since I am no expert in normalization it worries me a lot because it looks a bid crazy. I'd greatly appreciated if some experts here can comment on the correctness of my ERD design and also comment on performance and other future implications as it pertains to MySQL and Alpha Five.
    Attached Files Attached Files
    Last edited by rchen2001@hotmail.com; 08-14-2011 at 06:27 PM.

  2. #2
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: ERD Diagram for Property Management

    I wouldn't worry too much about Normalisation.
    Once Normalised, it might be as well to Rationalise - i.e. make the application easier to maintain in the future.
    It's an easy biuld in Alpha so why not give it a go and prove the design concept?
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  3. #3
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: ERD Diagram for Property Management

    While normalization makes databases more efficient to maintain, they can also make them more complex because data is separated into so many different tables.
    and
    Third Normal Form (3NF): No duplicate information is permitted.
    As Ted said - rather design for easier application maintenance.
    Honestly, I would suggest you first define your Entity Tables, use the ERD only to establish the linking fields .
    Normalization doesn't apply to ERDs but to database design. Use your knowledge of the flow of data (practical workflow) to create logical forms and let sets do the ER for you. Then focus on tables to handle billing and other event records.

  4. #4
    Member
    Real Name
    Rick Chen
    Join Date
    Aug 2011
    Posts
    18

    Default Re: ERD Diagram for Property Management

    Thanks for the reply. Perhaps I should have mention this is only one of the many modules for this project, the entire system will have about 50-80 tables. You are right I might as well just build it base on my ERD and see what happens. But it will be hard to gauge future performance and other implications if ERD is flawed, this is why I wanted to be sure of the schema. Any thoughts on the ERD?

  5. #5
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: ERD Diagram for Property Management

    Quote Originally Posted by rchen2001@hotmail.com View Post
    ... this is only one of the many modules for this project, the entire system will have about 50-80 tables.... Any thoughts on the ERD?
    In which case you have a partial ERD. Forget terminology, you seem to know what you want. An ERD is mostly fact - what relates to what, you have limited flexibility there. Performance is based on the database design, which you are not as strictly committed to - whatever you do that is inefficient can (usually) be redesigned.

  6. #6
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: ERD Diagram for Property Management

    One of the systems I work with has 2700 tables, of which 47 are used regularly. It's a pig - but not my pig thankfully.
    I created an Alpha app to construct Questions and Answer options to load into a highly normalized Oracle dataset. Doing it using Toad took far too long as so many tables needed to be addressed in a complex sequence. BT wanted it in ASP and C#. Took me a week and their developer 4
    Months.
    If this is an ongoing dev. build yourself a DD to handle things.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  7. #7
    Member
    Real Name
    Rick Chen
    Join Date
    Aug 2011
    Posts
    18

    Default Re: ERD Diagram for Property Management

    Quote Originally Posted by Ray in Capetown View Post
    In which case you have a partial ERD. Forget terminology, you seem to know what you want. An ERD is mostly fact - what relates to what, you have limited flexibility there. Performance is based on the database design, which you are not as strictly committed to - whatever you do that is inefficient can (usually) be redesigned.
    I think redesigning is my biggest concern. I am not very fond of redesigning after years of redesigning poorly conceived projects (not entirely my fault, clients are often partially to blame)
    But I suppose with Alpha it's much quicker and easier.

    So what would you suggest Ray? Just define and create the tables according to my knowledge of business logic, then using sets or linked content to create the links as needed?

  8. #8
    Member
    Real Name
    Rick Chen
    Join Date
    Aug 2011
    Posts
    18

    Default Re: ERD Diagram for Property Management

    Quote Originally Posted by Ted Giles View Post
    One of the systems I work with has 2700 tables, of which 47 are used regularly. It's a pig - but not my pig thankfully.
    ..
    If this is an ongoing dev. build yourself a DD to handle things.
    That is a fat pig! In comparison my 150 tables PowerBuilder app is just a hamster. No I don't intend on working on this forever, like 5 months and move on. What's a DD Ted?

  9. #9
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: ERD Diagram for Property Management

    I would say yes to that exactly - get it going without all the frills and cosmetics to prototype this segment. See that things will connect on forms and appear logical. Make copy forms to play around with concepts, and number them.. stuff like that. And then when you hit blocks ask relevant questions. IMO you passed the point of asking the board to comment on design, I don't know exactly what you want to achieve.

  10. #10
    Member
    Real Name
    Rick Chen
    Join Date
    Aug 2011
    Posts
    18

    Default Re: ERD Diagram for Property Management

    The client already seen and approved the prototype demo. It's the real deal now so hopefully not too much looking back. You are right, with Alpha it's really not that big a deal to go back and modify as long as the DB is fundamentally sound. I feel so much more confident to proceed now. Thanks again for your help!

  11. #11
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: ERD Diagram for Property Management

    A DD is a Data Dictionery. You can use it to get your thoughts together and agree the connections/keys/etc.
    I'm going to build a basic one and post it on the Forum. I'll put it here first, and I'll be starting in a few minutes so it's likey to be available tomorrow.
    What it does is help to get the ducks in a row.
    To do this it helps create Tables as you can add the Fields and the Field Rule comments - it doesn't build actual Rules.
    Once you have the design for each Table, you can create it using the Text version for Table creation.

    Glad you got the job from a Prototype - always the best way. When I got my first "biggie" I thought "Whoopie - Oh Bu66er - now I've got to do it!"
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  12. #12
    Member
    Real Name
    Rick Chen
    Join Date
    Aug 2011
    Posts
    18

    Default Re: ERD Diagram for Property Management

    Thanks Ted for all your help. My thoughts were pretty clear before I got involved with that ERD, then I got all concerned with too many theoretical and practical implications of 3NF. The more I research the more issues comes up lol. You guys really helped me clear my head. Much obliged.

  13. #13
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,628

    Default Re: ERD Diagram for Property Management

    Is this a desktop app or a web app?

    I'm curious how Ted's DD applies to MySQL?

    FYI: I've had difficulties using a db schema created in WorkBench and using it with linked grids. Somehow the MySQL linkage and the grid linkage conflicts - at least in my experience.

  14. #14
    Member
    Real Name
    Rick Chen
    Join Date
    Aug 2011
    Posts
    18

    Default Re: ERD Diagram for Property Management

    This is a web app. I'll take that WorkBench / Grid linkage conflict into consideration. Thanks Peter.

  15. #15
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: ERD Diagram for Property Management

    Well this might scupper the plan.
    The Tables can be uploaded into SQL I believe, but the attached DD is really a Desktop design aid item.
    It's an ongoing free development with a Bug button to mail me.
    Login is AUser
    Password is Guest.

    If you are watching Stan, I'd appreciate a bit of help with the table_create feature code.
    Attached Files Attached Files
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  16. #16
    Member
    Real Name
    Rick Chen
    Join Date
    Aug 2011
    Posts
    18

    Default Re: ER Diagram (New) for Property Management

    Thanks Ted.
    Last edited by rchen2001@hotmail.com; 08-16-2011 at 07:56 AM.

  17. #17
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: ERD Diagram for Property Management

    Hi Ted.
    Here is some code. I took far too long for what it looks, but have tested it in your database with a couple of fields numeric and char.
    No validations. No filters. Just the create functionality
    Code:
    dim newtbl as c
    dim fieldlist as c = ""
    tbl = table.open("fld_data")
    tbl.fetch_first()
    NewTbl=tbl.tbl_name
    while .not. tbl.fetch_eof()
      fieldlist=fieldlist + tbl.fld_name+"," + tbl.typ_val +"," + tbl.length + crlf() 
      tbl.fetch_next()
    end while
    create_table(NewTbl,fieldlist)
    file_add_to_db(NewTbl+".dbf")
    I entered some new fields through your front end, then ran this code from CP
    One could further add fields and change field names later

  18. #18
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: ERD Diagram for Property Management

    Great Ray, many thanks.
    I'll plug it in tomorrow and try it.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  19. #19
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: ERD Diagram for Property Management

    Ray, couldn't get it to work.
    I ran it from a Form and also from the Code Editor. No errors, but no table either.
    Can you let me know how you ran it please?
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  20. #20
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,990

    Default Re: ERD Diagram for Property Management

    When I got my first "biggie" I thought "Whoopie - Oh Bu66er - now I've got to do it!"

    My first "BIGGIE" was really a small app for Shell Oil Company and it was written in Clipper. I gave them a price for 3 months work. It was really done in 2.5 weeks and that was part time. Got lucky and sold the app to a second Oil company for the same price and a third one for considerably more. It all happened in about 4 months. Thought I was rich.

    LOL
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

  21. #21
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: ERD Diagram for Property Management

    Attached screenshot
    1. Tables before
    2. Open form. Enter Table name and some fields. Close Form.
    3 Run the code - The new table appears in the list and open for record entry.


    [ATTACH=CONFIG

    Attached also the database]29104[/ATTACH]
    run the program "newtblcreate"
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Ray in Capetown; 08-18-2011 at 04:16 AM. Reason: program name inserted

  22. #22
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: ERD Diagram for Property Management

    This is what I get Ray.
    Attached Images Attached Images
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  23. #23
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: ERD Diagram for Property Management

    It looks like your version is leaving spaces after the table name - only when adding it to the database.
    try change
    Code:
    NewTbl=tbl.tbl_name
    to
    Code:
    trim(NewTbl=tbl.tbl_name)
    It would seem that the table has been created - look in your browser

  24. #24
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: ERD Diagram for Property Management

    It'sd created OK Ray, but you have to actually do and Add Table/Set manually.
    Tried various coding options but getting the Table in the Current Db CP, is proving elusive.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  25. #25
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: ERD Diagram for Property Management

    I cant replicate the problem here - Many alpha5 operations use file_add_to_db()
    It is may be to do with the relative table name in your database.

    That will resolve with filename_decode()
    Change the line
    Code:
    NewTbl=trim(tbl.tbl_name)
    with
    Code:
    NewTbl=filename_decode(tbl.tbl_name)

  26. #26
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: ERD Diagram for Property Management

    Well we are getting there. (well with your help and persistence).
    FILE_ADD_TO_DB("D:\Alpha 5 Test Bed\Alpha Test bits\DataDictionery1\my table.dbf") creates and displays the Table I want.
    The [my table.dbf] is hard coded. How do I get the variable [NewTbl] in there instead?
    Also, the Path to the Database could be optional?
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  27. #27
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: ERD Diagram for Property Management

    FILE_ADD_TO_DB("D:\Alpha 5 Test Bed\Alpha Test bits\DataDictionery1\"+trim(newtbl)+".dbf")

    Remember to make it generic in the case of the data dictionary creating the new table outside of the new database?

  28. #28
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: ERD Diagram for Property Management

    This is the final working version Ray. Why it didn't work the first time is probably the Decide line.
    I'm going to add a few selection options using xdailog (sob) so I'll keep you posted.

    dim newtbl as c
    dim fieldlist as c = ""
    tbl = table.open("fld_data")
    tbl.fetch_first()
    NewTbl=tbl.tbl_name
    while .not. tbl.fetch_eof()
    fieldlist=fieldlist + tbl.fld_name+"," + tbl.typ_val +"," + tbl.length + crlf()
    tbl.fetch_next()
    end while
    NewTbl=filename_decode(tbl.tbl_name)
    create_table(NewTbl,fieldlist)
    FILE_ADD_TO_DB(NewTbl+".dbf")
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

Similar Threads

  1. Calculated field / Text property vs. Field Property
    By Donald Duck in forum Alpha Five Version 10 - Desktop Applications
    Replies: 2
    Last Post: 11-05-2010, 08:46 AM
  2. Diagram Tool
    By Citadel in forum Application Server Version 10 - Web/Browser Applications
    Replies: 1
    Last Post: 10-23-2009, 02:36 PM
  3. ERD Software - what are others currently using?
    By Citadel in forum Alpha Five Version 9 - Desktop Applications
    Replies: 0
    Last Post: 08-28-2009, 06:17 AM
  4. Any way to display an ERD diagram of an A5 DB?
    By keuler in forum Alpha Five Version 9 - Desktop Applications
    Replies: 4
    Last Post: 11-17-2008, 10:22 AM
  5. ERD Modeling
    By acant05 in forum Alpha Five Version 5
    Replies: 0
    Last Post: 08-29-2002, 03:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •