Results 1 to 9 of 9
  1. #1
    TS21 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    4

    Database modeling

    Hello Community,

    I'm new with databases and have to do a database for my internship/seminar-paper. The company I'm working for gave me the task to create a database with their competitors.

    At this time I'm working on the "basic model" of the database. I created the first concept but I have no idea if it is right or wrong. It would be nice if someone could take a look at it and give me a feedback.



    The direction of the arrows should illustrate a 1:N relation. The words which are underlined should symbolize the primary/foreign key.

    Since I'm using a PC from the company for the task I wasn't able to install a software for sketching a database model so I searched for an online-option.

    I hope it is readable.

    With kind regards
    Attached Files Attached Files

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks OK in principle

    note that Type, Field and Year are reserved words and should not be used for table and field names. Would also avoid having spaces in those names as well - use the caption property if required.

    Would also consider you probably don't need the Year table anyway, just store the year

    With regards the lookup tables - country, field etc where basically all you have is a description and a pk, although this fits the normalisation rules I prefer a slightly non normalised arrangement to just store the description as the primary key, but not relate it to the table where it is referenced - so the table stores the description directly without having to look it up - a kind of 'dotted line' relationship.

    This simplifies queries and so potentially improves performance and generally has little impact on database size. If there is a spelling correction required then some simple code in the after update event can update the main table(s) rather than relying on the cascade updates attribute in the relationship. You would still select countries using a combo with a rowsource of the country table and the same functionality to add new countries.

    Not sure if you are using access at this stage but in database tools, click on Relationships and you can define them there - to print, click on database documenter, then the current database tab and select relationships

  3. #3
    TS21 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    4
    For the tables I'll use the prefix tbl in front later, this was just the first, simple, basic plan to figure out if I'm on the right way. I used a better form in my native language, but the translation to English got a bit poorer.

    I'm not quite sure if I get the "dotted line" relationship, since I'm new to Access and tried to follow the normalization rules.

    I'm not using Access so far, I wanted to draw the skeleton first since most training sources for Acces recommended this step before starting to put the model in Access.

    So in general the idea of the database is okay and I could create these tables in Access?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm not using Access so far, I wanted to draw the skeleton first since most training sources for Acces recommended this step before starting to put the model in Access.
    best way to do it

    So in general the idea of the database is okay and I could create these tables in Access?
    It looks OK, subject to my comments - only you can say it is OK.

    The other part of designing the schema is to think about the process and business rules - how easy will it be for a user to enter detail? how does the data come in - manual input, import from a source etc?

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Just a note on what ajax said, I prefer tables with descriptions like

    Code:
    Country
    idCountry  CountryName
    1          USA
    2          Mexico
    and store the idCountry in any table where it's relevant even if the list is short because of the correction in spelling issue ajax mentioned.

    I counteract that by having an 'options' table

    i.e.

    Code:
    tblOption
    Opt_ID  Opt_Group  Opt_Desc  Opt_Sort
    1                  GeneralYN 
    2       6          Yes       1
    3       6          No        2
    4                  Phase    
    5       4          Phase 1   1
    6       4          Phase 2   2
    7       4          Phase 3   3
    8       4          Phase 4   4
    so all my options with simple descriptions are on one table and I pull up the options based on the option group (generalYN, Phase, etc.).

    I also do not favor setting up relationships through the database itself, but that is a personal preference, it is not a necessary step but may help you in the early stages of your database building.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am in agreement with rpeare with regards to tables like Country.
    In the PDF, you have
    Code:
    Country
    idCountry
    Country1
    Country2
    Remember you are looking at fields, NOT data. So having fields like Country1, Country2 are repeating fields and break the rules of normalization.
    I would use rpeare's example is
    Code:
    Country
    idCountry  
    CountryName
    I see a 3 tables with compound primary keys. I never do this. I know it is used to limit duplicates, but, IMO, it better to have a PK field and if I need to limit duplicates, I will use a compound index.

    Maybe these will be of interest:
    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers

    Use Autonumbers properly
    http://access.mvps.org/access/general/gen0025.htm

  7. #7
    TS21 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    4
    Hello,

    @Ajax
    the plan was that the company would Import the data with an excel sheet but they didn't save any sheets so far so I'll do a form in Access where they can put the data manual.
    Thanks for the help so far

    @rpeare
    actually I wanted to Show that way with my drawing. It was a mistake in my sketch, I was planning to store the country data etc like you showed it.
    Thanks for the help so far

    @ssanfu
    I still had my problems with PK and FK while I was creating the model, actually all columns with id in front should be a fk instead, beside their origin table, there it should be the id.
    Thanks for the help so far and thanks for the links, I'll check them out

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    all columns with id in front should be a fk instead
    Hmmm. I don't think that would be considered a "rule".
    Here is a model from Barry Williams' site (databaseanswers.org) that shows some PK and FK
    structures.
    Click image for larger version. 

Name:	PK_FKInAModel.jpg 
Views:	19 
Size:	95.8 KB 
ID:	25802

    Good luck with your project.

  9. #9
    TS21 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    4
    Good Evening,

    I read your posts of the thread and tried to create a newer version. Also I would like to tell you a bit what I expect of the database to know if I'm on the right way.

    in the End the database should be able to add competitors, also I want to select a a "Field of operation" and see all the companies who are in the same field, then I'd like to compare 2 companies. I also would like to add diagram which compare the two selected companies in "number of employees" and "total turnover" over the last few years. It would be also nice to create a form where I can choose a few companies to just put them in a diagram together.
    Am I on the right way for that with my database?

    Ratio:

    in this field I'd like to add a forecast for the running year, after the year is finished the forecast should be replaced by the real number for this year and the forest can be set for the next year (it should be done manually). I guess I need a new table for that? something like "RatioForecast"?

    Sharing:

    This table a extend table for the Owner, if the company is , for example, in a diversified holding, I want to put the main actors there with the shares they have of the company and since when they have holdings of the company.

    Sector Turnover:
    Here I want to put a few individual sectors for every competitor which I want to fill in with the year and the turnover of the sector.

    Portfolio:

    This table should be similar to the Sector Turnover table, here I want to put individual Products of the company with their turnovers and number of sales for each year.

    Financial Reports:

    In this table I want to put the different reports for the company. It should show if the report is available and if it is available it should be saved somewhere so you can use it later in a form take a look at the different reports a company offers. I guess it is possible to save these reports in a extra field as an attachment, but the table should need a free column behind every different report to store it, or am I wrong?
    Distribution:

    These information should be put once in the table and can be edited and updated if there are new information, it shouldn't be depended on the year, just on the competitor. Behind every information in this table there should be written the source where this information came from. I think about that the same way like I do with the financial report. I would put a free column behind every information to store the source there. but I don't think this would be the right way.

    There are a few more tables but they all work like the Distribution table, so the tables I put in the graphic are the most important ones.

    Thanks for your time and your help, I really appreciate it since I don't really know a lot about databases.
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-27-2016, 06:20 AM
  2. Replies: 1
    Last Post: 07-01-2015, 04:18 AM
  3. Replies: 1
    Last Post: 03-21-2015, 11:55 AM
  4. Some help with conceptual modeling
    By Benji in forum Database Design
    Replies: 2
    Last Post: 09-10-2014, 03:43 PM
  5. with regard to Database Modeling
    By johnseito in forum Access
    Replies: 4
    Last Post: 11-18-2013, 01:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums