Results 1 to 10 of 10
  1. #1
    Zerameth is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    2

    Relational assesment, data modelling and table queries for simple list

    Hi everyone,

    I have an excel sheet with 5000 rows which I want to export into MS Access as I realised it was becoming unmanageable and wanted a better data storage option.
    But as a complete beginner, I’m struggling to visualise how I would structure this database, in terms of data modelling. The data consists of:

    • a list of world destinations with a science theme and includes several ‘categories’, e.g. accommodation / natural world / science centers / historical places.
    • all the records have SOME common fields, such as country, address, GPS coordinates, description, contact name, web url etc
    • but each category also has fields which don’t apply to others – e.g. only the accommodation category will have sub-categories such as ‘hotel’, ‘B&B’ and ‘camping’ and often ‘room capacity’.


    • each country will have many records across all categories – I will be giving my team individual countries to manage/keep updated - or even split into regions for larger countries, but I haven’t looked at forms yet.


    Can anyone advise, please? I can obviously adapt the excel sheet accordingly. So….

    • Do I stick to one table with lots of empty fields?
    • Should I have a table per category (or even more tables) - and which other benefits would that give, apart from fewer empty fields?
    • I can't see many relational aspects i.e. ALL records are simply ‘science destinations’ at their basic level. Apart from Contact Names below:


    Contact Names - this is the only part I think I can figure out:

    • Each contact name may cover more than one destination, but this will only apply to about 1 or 2% of the records – most records will have unique contact names.
    • Some records may have multiple contact names
    • Some records will have NO contact name, but I can input N/A or TBC.




    So I’d need a separate table for Contact Names, despite the vast majority being unique, right?

    Otherwise, I can’t get my head around the rest of it.

    Finally, this data is for loading into a web directory, Directories Pro. My concern is that having multiple tables will make that process difficult or impossible. I appreciate this is not specifically applicable to this forum, but if anyone CAN offer any help or advice, I’d be so grateful. This is a one-off project only, so I’m trying to avoid cluttering my poor brain with knowledge I don’t yet need. I’m happy to learn as I go once I’ve got a starting point.

    Many thanks for any guidance.
    Zerameth

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Attached is a white paper on Data Normalization for Relational Data Bases
    Attached Files Attached Files

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    In addition to Alan's post and given your comments

    but each category also has fields which don’t apply to others – e.g. only the accommodation category will have sub-categories such as ‘hotel’, ‘B&B’ and ‘camping’ and often ‘room capacity’.
    Do I stick to one table with lots of empty fields?
    Should I have a table per category (or even more tables) - and which other benefits would that give, apart from fewer empty fields?
    the example db in post#10 https://www.accessforums.net/showthread.php?t=84779 may be of assistance - substitute category for attribute

  4. #4
    Zerameth is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    2
    Thanks Alan, I've been reading similar articles and am slowly learning - but I am a complete beginner.

    My struggle is applying these concepts to my data because (to my eyes) it's different from all the examples I'm seeing, e.g. sales/customers, inventory etc.

    Ajax, thanks also - I've taken a look and I still can't picture my own data in that context. (I realise I have a massive mental block - I can't even use this forum properly yet!).

    I've added a mock up, in case it helps: Zerameth Excel data for dBase Mock Up.zip
    Can you look at that, and give me an example of 'replacing category with attribute'?

    Btw, I wasn't 100% correct before. Even the address, GPS and contact fields will be N/A for some categories - the only 2 short fields which are 100% populated are Category and Name.

    In case I wasn't clear before, I'll need to:
    a) be able to add/remove/amend records without altering the structure and to create forms for individuals to use for 'their' area
    b) upload records to Directories Pro directory on my website.

    I need to keep it simple and I can even stick to just one contact name, if it's getting messy.

    I don't anticipate any/many queries - there are no transactions occurring, for example.

    If there is any help at all that will enable me to bypass lengthy articles, I'd be very grateful. I'm not lazy, but I'm just not great at abstract thinking/theory!

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Zerameth,

    Lots of articles and tutorials in the Database Planning and Design link in my signature.
    The tutorials from RogersAccessLibrary are quite good for identifying tables and relationships from a business description.
    Good luck with your project.

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    a tip concerning queries:
    I don't anticipate any/many queries - there are no transactions occurring, for example.
    After having decided how to design your tables and relations, you'll need at least one query to collect your data from the different tables and put them together in the correct way to export them to your website.

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    You mention Sales/Customers which most people understand.

    The normal data Input for this type of Process is :-

    A Customer on a specific Date will Order a number of Items.

    To normalise this we would need tables as follows:-

    Customers
    Orders
    OrderItems

    Could you break down your Excel example to give us an understanding of your Data Input?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Zerameth,

    I suggest you work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the Database Planning and Design link in my signature. He shows a procedure to take a business description and to identify tables and relationships and Normalization.
    The tutorials have problem definition, instructions and solutions. This process is like developing a blueprint(model) of your tables and relationships to support the described business. Work through a tutorial and you will learn, and what you learn can be applied to your or any database.
    Good luck.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It sounds like you might be looking at some kind of reservations system.

    Might also check out the "Data Models" site.
    There are three columns. In the right most column, scroll down until you see "20 Reservations". There are 31 reservations dB examples underneath that heading.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Can you look at that, and give me an example of 'replacing category with attribute'?
    having now seen the data it is quite difficult to determine the rules and perhaps my suggestion is not appropriate. If this is all the data then it is probably not worth trying to normalise it

    But if you were it looks to me like name would map to location and would include fields for owned/run by, GPS and address and country, categoryFK and subcategoryFK

    Attributes would be type, subtype, room, seating, equipment and that would be linked to subcategory in the location

    The other fields I don't know, too many N/A's and inconsistencies - why would Davos not have an owner for example?

    Hope this helps you to start being able to see the wood for the trees, good luck with your project

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

Similar Threads

  1. Data Modelling for Car Dealership Data - New to Access
    By mismag in forum Database Design
    Replies: 9
    Last Post: 02-26-2019, 09:11 PM
  2. Asking about data modelling
    By jaryszek in forum Database Design
    Replies: 6
    Last Post: 03-15-2018, 06:49 AM
  3. Replies: 8
    Last Post: 03-10-2018, 04:50 PM
  4. Simple Relational Database - Please Help!
    By hannahskellam in forum Database Design
    Replies: 2
    Last Post: 05-09-2013, 11:47 AM
  5. Simple Question about Multiple Table Queries
    By Access_Headaches in forum Access
    Replies: 4
    Last Post: 02-13-2012, 08:36 AM

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