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

    Exclamation Unanswered: Please improve my Database model of Employees with magnetic cards

    Hello, i must develope database with Employees and their Area access magnetic cards for my Huge organization. There will be a database file on One PC, and ONLY one user will access and work with it on the SAME PC.

    I must also develope C# Windows application which will access, and modify data on this database file in the PC.

    Problem is i'v never worked before with Access, or any other database and database programming.

    I have already read one C# ebook, and almost read another eBook about relational databases during these 2 days. And i must complete my project as soon as possible so that my Boss can see it soon, otherwise it will be bad.

    Ok, i have done something, now i just need your advices, and help on the improvement.

    Here is the idea of my Database:

    1.Our fast growing Organization have few Divisions now.
    2.Every Division has many Departments.
    3.Every Department has many Employees.
    4.Every Employee has one Title within his Department.
    4.Every Employee has one MagneticAccess card.
    5.Every Card may have one or many area permissions.
    6.Number of permission Areas will grow soon.

    so, in other words, every Employee has MagneticIDcard, this card grants access for one, or many areas. Magnetic Card has an expire date, and an issue date. Number of Employees will grow, so the Number of cards also will grow. Number of areas also will grow. Number of Titles (specialities) within Departments will also grow. Number of Departments and Divisions also will grow.The most growing will be the Employee number.

    Total number of Employees may grow up to 50 000. The database file will be on One PC as i said, and will be accessed by one man on same PC.

    first question is: Will MS Access be able to handle such a database which will be accessed through C# App, when the number of Employees will be around 50 000?

    And please, help me to interconnect all the relations between the tables in my Database. And say me what you think about my Database organization? Are there unwanted fields in tables? Are the primary keys set correctly? and what kind of relationship must be betweent every table?

    EmployeeID,TitleID and AreaID are Autonumbers.
    (i heard that not good to use autonumber for Primary key from one of the posts,so i will remove it,but then tell me what should be primary key in e_employees?)

    The relationships from e_subholdings to d_departments, from d_departments to e_employees, and from e_employees to c_cards are ONE-TO-MANY, is this Good?

    if no, then what it must be, and what others must be?

    now i am attaching a snapshot from the relationships of my Access 2007, so that you see the whole picture of my Project. And thanks in advance for your Help!
    Attached Thumbnails Attached Thumbnails rel1.gif  

  2. #2
    Join Date
    May 2006
    Posts
    18
    Hi,

    >and will be accessed by one man on same PC.

    In this case I'd say Access should fit your needs.
    Points of attention

    1) if you think that in a (relatively) short future you'll need to put the .mdb in a shared directory on the LAN to be accessed by other people, consider another, "real" database engine

    2) Remember that Access file is limited in size (2Gb)-so it's not about the number of records in your tables, but about the size of the mdb file.
    In reality, even being on your hard drive, a file with a size above 1Gb can become clumsy.
    So make sure you can estimate the growth of the size of the database to avoid surprises.

    >i heard that not good to use autonumber for Primary key
    I'd like to heard the reasons why not? If you ask me I'd say it might depend
    on the nature of the table. If it's just a static 'lookup' table then you might avoid using autonumber and create your own PK. But if the table is heavily used for insertion, etc, why not let Access do the job for you?

    >...ONE-TO-MANY, is this Good?
    it's not that one-to-one is good and one-to-many is bad as such, or vice versa after all it all depends on a task at hand

    (At a first glance) remark on the diagram

    -areaid is PK in r_rights and r_areas, if I get it in r_rights 'AreaId' should be a FK of AreadID in 'a_areas'




    Shurik.
    Last edited by Shurik12; 11-09-07 at 18:10.

  3. #3
    Join Date
    Nov 2007
    Posts
    43
    thanks for the Information Shurik12.

    and what you guys think about how is it better to relate tables to each other?

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Your tables are a model of the real-world situation you are in. You use the relationships which allow you to accurately model the real world.

    Personally, given the situation that you describe, I would probably use MSDE or postgresql to start with. That way, it will be dead easy to migrate it to a later, when you would outgrow Access..
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Nov 2007
    Posts
    43
    thanks, but i would like to see anyway, how would you, the experienced guys relate those tables due to the current problem?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the PK in r_rights should be (CardID,AreaID), i.e. both of them together is the PK

    both are individually FKs to their respective tables

    do no use three columns for day, month, and year -- use a single date/time column

    relate e_employees.TitleID to t_titles

    other than the above, your diagram is fine

    i personally do not like the d_, t_, r_, e_ prefixes, but that is a minor point

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

  7. #7
    Join Date
    Nov 2007
    Posts
    43
    r937 thank you!

    You said to use a single date/time column, but i choosed to separate them because....for example, i may want to know, which Cards of which Employee expires...say in September? how can i do it if i combine 3 fields of data into one?

    and another question is, what mean the following options in the Relationship creation dialog?

    1.Enforce Referencial integrity
    2.Cascade Update related fields
    3.Cascade delete related records


    and one more thing, how to set Primry Key in both fields? when i set Primary Key in one field, and then doing the same with another, the first field is not Primary Key anymore...how do you do that?
    Last edited by TeslaShock; 11-10-07 at 03:03.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TeslaShock
    for example, i may want to know, which Cards of which Employee expires...say in September? how can i do it if i combine 3 fields of data into one?
    Code:
    where month(expiry_date) = 09

    Quote Originally Posted by TeslaShock
    and another question is, what mean the following options in the Relationship creation dialog?
    these are basic relational integrity concepts

    see Integrity Rules in this article


    Quote Originally Posted by TeslaShock
    and one more thing, how to set Primry Key in both fields?
    open the table in Design view, highlight both fields, and press the little key icon

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

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    A homework question disguised with references to "a boss" and C# reference manuals. The methods get more original by the year.

    "And i must complete my project as soon as possible so that my Boss can see it soon, otherwise it will be bad."

    That attempt I must confess was a little better than others that I have seen, especially the otherwise it will be bad part.
    Last edited by r123456; 11-10-07 at 04:45.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Nov 2007
    Posts
    43
    Well, i decided to improve my model a little more. I have created one more table for Titles, cuz any name of title can be changed anytime. So, one Employee will have only one Title, but one title may have a lot of employees associated with it.

    i also added a field of CardName in c_cards table, because officers will give us the names of cards (letters and numbers). So i'll use CardID field for myself, but CardName to show the Card name in program. [card name will never change] .

    and special thanks to you R937, i've read your manuals too.

    after all i would like to post again my almost Final model of database, and i would like you guys to take a look at it, and say is it now perfectly good, or anything else could be done?

    thanks in advance!

    p.s. and yOu, r123456 if you wanna show how clever you are go and talk in Chit Chat forum. I didnt ask anyone to do my "homework", i am doing it myself, i just asked for advices and suggestions on improvement. if you have eyes you can read it.
    Attached Thumbnails Attached Thumbnails rel2.gif  

  11. #11
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I didnt ask anyone to do my "homework", i am doing it myself, i just asked for advices and suggestions on improvement.

    What you're saying is, more or less, I asked people to do my homework for me. That's not a problem (well it is, but not in the context of my reply), I just thought I'd highlight the amusement it gives to read how you tried to disguise the fact.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by TeslaShock
    i also added a field of CardName in c_cards table, because officers will give us the names of cards (letters and numbers). So i'll use CardID field for myself, but CardName to show the Card name in program. [card name will never change] .
    So the "CardName" can uniquely identify each card?
    Surely this should therefore be the primary, instead of some abritrary integer value...
    George
    Home | Blog

  13. #13
    Join Date
    May 2006
    Posts
    18
    To TeslaShock
    Quote Originally Posted by TeslaShock
    >and i must complete my project as soon as possible so that my Boss can see it soon, otherwise it will be bad.
    >I didnt ask anyone to do my "homework", i am doing it myself
    Very nice indeed. May be you could tell the truth from the very beginning next time?

Posting Permissions

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