Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2007
    Posts
    43

    Exclamation Unanswered: Is this database design is good?

    Hello, i am implementing database model of my organization, with records of the Employees, and their Area access cards.

    My organization will have some divisions.
    Every division will have some number of departments.
    Every Employee will have any title.
    Every Employee will have one (maybe more...but doubt it) card for area permission.

    Every card may have a right for one, or many areas.


    i am attaching the relations schema,
    and i want you, the proffesional guys take a look at it, and say how good is my database design according to my needs.

    thanks in advance, i will wait for your reply, its very important to me.
    Attached Thumbnails Attached Thumbnails rel3.gif  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why are you repeating your homework assignment from two weeks ago?

    http://www.dbforums.com/showthread.php?t=1624261

    what have you learned from that previous thread?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2007
    Posts
    43
    well, i had few remakes over the database model, and also in database concept. but in a whole it is same, i also found out how to make drop-down lists in the table edit mode(to input data more easily).learned how to make query by any part of the date. and i was learning C# together with ADO.NET programming.

    Application part of my project will come in play later. but now i must have workind database, to be able already to enter our data.

    that is why i have taken screenshot of my last design schema and asked just to say your opinions about it. cuz in few days i will begin entering the data there...and it will grow.

    so?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TeslaShock
    so?
    so go back to my reply in the other thread and re-apply my suggestions

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2007
    Posts
    43
    i looked carefully that old thread, all your suggestions are applied.

    so, do you think now that my design is OK?

    and one more question....what if i will remove c_cards table, and put its data in e_employees table, and then relate EmployeeID, to the EmployeeID(changed from CardID) in the r_rights table?

    i said it now cuz, some time ago i have been said that maybe one Employee will have ONLY one card...
    then this card info. may be stored in employees table... what you think?

  6. #6
    Join Date
    Nov 2007
    Posts
    11

    Design considerations

    Hi,

    Took a look at your design and here are some thoughts. I haven't gone to previous threads mentioned so if I'm repeating what's already been said, sorry.

    My guess is that employees might work for more than one department at a time or move from one department to another. If this is the case, then a many-to-many interesct table between employee and division help.

    Q: is the card area related to the department? that's not reflected in the model.

    Q: will auditing ever take place to track an employee's movements? How is that history to be kept?

    Q: are there there time-of-day restrictions?

    Your business rules as you had defined do not fully explain the fields and the rules.

    I'm not sure what organization this is reflecting. It seems, as mentioned earlier, a model for a school project. If it is fact an actual project to be used by an organization, be sure to question carefully all the details of how the data is to be used and how the items relate.

    hope this helps.

  7. #7
    Join Date
    Nov 2007
    Posts
    43
    well well well... thanks for your reply! things changed in my database concept! there will be no records for departments! no restrictions on time of day... and one employee may work only under ONE division... here is my new design attached in the file!
    there will be no auditing for moves... for tracking...etc

    and one employee will have only one card... and...thats why i decided to remove c_cards table....
    Attached Thumbnails Attached Thumbnails rel3.gif  

  8. #8
    Join Date
    Nov 2007
    Posts
    11

    removing card table

    Basing an assumption that a data card is required to gain access, If you remove the card table, how is access granted to the areas.

    Is the card the same as a hotel key where it is disposable and no tracking is necessarily done? Assuming that this method can encode cards that can access both places.

    If an organization needs to track the cards issued, then a card entity is required. I'm also making the assumption that the card is carrying access information via a chip or magnetic strip. The model reflects the business rules so you are the best judge.

    good luck with this.

  9. #9
    Join Date
    Nov 2007
    Posts
    43
    well, i must know in database, WHICH Employee has access to WHICH areas... and there will only be one field CardExpireDate in Employees table..which says when card expires... this database design allows me to do it... for example, and when i press + on the employee record, i see drop down table of list to which areas he has access...

    there will be NO tracking! i only must know to which areas my Employee has access... so for this simple purposes isnt my design OK?

  10. #10
    Join Date
    Nov 2007
    Posts
    11
    With the design as it is, it should work for your puposes.

    Because the card has an expiry data, name, and letter name; this indicates it is its own entity and therefore really should have it's own table.

    Keep in mind, I don't know all the rules or requirements for the data. You are the best judge. Try some test data and see if it does what it needs. That way you'll know.

    Cheers

  11. #11
    Join Date
    Nov 2007
    Posts
    43
    well, as i see it does what i need, from my database model it seems that Employee has access rights for areas...not a card.. and cardexpire date is just one more field which every employee will have...(as a phone number, or date of birth)... so as i see it works....just wanted your opinion. thanks for help

Posting Permissions

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