Results 1 to 7 of 7
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Client and multiple tables and records

    Hi Guys,

    how are you?

    I am working on database model design now and have issue how to do it in the best optimal way.

    I have 3 table in attached example.

    Clients, MemorySize and OperatingSystems.

    Now each client can have multiple different records.



    Client A has Memory 256 and 512, Client B has Memory 1024 and Client C has memory 2 TB.
    Client A has Windows and Ubuntu and Linux, Client B has Linux, Client C has Windows and Ubuntu.

    How to create relationship in Access to easily handle it?

    Please help,
    Jacek
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you only need the 2 tables:
    tClients table should link to
    tDevices table

    tDevices looks like:
    ClientID
    DevType (pc, laptop, scanner, etc)
    Make
    Model
    OS
    Memory
    SerialNo

    set the relationship to 1:many
    then the user can have many PCs or laptops, or monitors

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    thank you ranman256.

    What if i would have about 20 tables with different kinds of Devices? (Memory, OperatingSystems, DevType, SerialNo, MonitorType, Graphics etc.)
    Should i put it also in one big table tDevices?

    Jacek

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont.
    all devices can fit in tDevices.
    just make extra fields that you need.
    pc types have memory
    modems may have other properties
    monitors, copiers,etc....all should be in tDevices.

  5. #5
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    ranman256.

    thank you.

    I meant more about having 20 separate table (like Memory, OperatingSystems) so in t_Devices i will have about 20 columns.
    It is ok ?

    Jacek

  6. #6
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Apr 2015
    Posts
    128
    Quote Originally Posted by jaryszek View Post
    ranman256.

    thank you.

    I meant more about having 20 separate table (like Memory, OperatingSystems) so in t_Devices i will have about 20 columns.
    It is ok ?

    Jacek
    You can use any many columns as you need to describe an item in that table. Table design should be "subject-based." Anything in the same "subject" should be in that table. Each record in a "Devices" table should describe components of a device: OS, CPU, memory size, hard drive size, brand, model, serial#, etc.

    You create separate tables for operating systems and memory types only if you want to include a lot more info about OS and memory, such as OS manufacturer, OS family, OS version, open- or close-source, OEM or retail, etc., and for memory, things like DDR 3, 4, 5, etc., type (PC3-10600, etc.), no. of pins (240 pin, 204 pin, etc.). You normalize these tables with unique IDs and refer to them in the Devices table.

  7. #7
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    thank you keviny04!

    It is very helpful !
    I am closing the topic,

    Best Wishes,
    Jacek

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

Similar Threads

  1. Multiple records for same client
    By aymanmb in forum Access
    Replies: 2
    Last Post: 12-09-2013, 10:50 AM
  2. Count records from multiple tables
    By jazzin in forum Queries
    Replies: 1
    Last Post: 07-24-2013, 08:16 AM
  3. Replies: 2
    Last Post: 10-08-2012, 06:40 PM
  4. Inserting records in multiple tables
    By Nikos in forum Database Design
    Replies: 8
    Last Post: 02-17-2012, 02:35 PM
  5. Replies: 3
    Last Post: 01-05-2012, 12:04 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