none
Table structure advise RRS feed

  • Question

  • Dear all,

    I need to build table structure in order to configured customer machine systems. In order to express my need using a clear exemple here is my scenario :

    - I have a set of system Images which are based on a list of components and a list of parameters.

    - The idea is that from those base image I need to configured customer PC whith a custom system image unique for each customer 

    SystemImageReference table represent the list of SystemImage like : Server2003, SQL server, Linux, Windows10
    Components Table represent a list of components that cold be used by Any SystemImage reference
    Parameters Table represents a set of default parameters used by the SystemReferenceImage

    Those 3 tables represent the base reference tables that will be used to create any client customer PC

    ClientImage table linked to SystemImlageReference represents a unique client records for each customer in order to know based on a customer ID, which based reference imgae has been installed to that client

    At the end from the CLientImage table I should have the possibility to uniquely identify the customer configuration that has been deployed with ImageReferenceId, list of Components and default parameters which have be set

    I have difficulties to represent table fields and relation to reach my scenario, any one could drop me some advise and sample appraoch on this ?

    Thanks for help

    regards 

    Tuesday, July 17, 2018 8:08 PM

Answers

  • Component  should have reference to SystemImageReferencetable , so I imagerefernece has many complements 

    create table Component

    (

    componentid int...

    component name ,,,,

    imagereference

    )

    Now , probably  components have parameters , am I right, so parameters table should have reference to the  Component tables

    And by join SystemImageReference with  Component on imagereference and then join Component with parameters  on componentid you should able to get all the data to the unique customer


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 18, 2018 5:57 AM
    Moderator
  • if you want to have maximum flexibility design should be something like this

    SystemImageReference table represent the list of SystemImage like : Server2003, SQL server, Linux, Windows10
    
    Components Table represent a list of components that cold be used by Any SystemImage reference
    
    Parameters Table represents a set of default parameters used by the SystemReferenceImage
    
    Bridge table linking SystemImageReference to Components and Parameters - This will indicate which components were part of image and what parameters are set for it. ImageID, ComponentID and ParameterID will be added in this table as FKs to point to the corresponds reference tables. Also there will be multipl entries per SystemImage based on number of Components and Parameters involved
    
    
    ClientImage table- this will have ClientID and ImageID as FKs pointing to Client table and SystemImageReference table


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 18, 2018 6:31 AM
    Moderator
  • @Visakh16

    I do not understand this line :
    ClientImage table- this will have ClientID and ImageID as FKs pointing to Clienttable and SystemImageReference table

     What is client table ? I have only ClientImage whihs from whish I should be able to get customer configuration from ImageReference, component and parameters

    How can I get thos information from your sample ?

    Client table will be the table which holds details of customers for which the images were allocated 

    I just used the naming as clients. you can use customers or users instead :)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 18, 2018 7:11 AM
    Moderator

All replies

  • Component  should have reference to SystemImageReferencetable , so I imagerefernece has many complements 

    create table Component

    (

    componentid int...

    component name ,,,,

    imagereference

    )

    Now , probably  components have parameters , am I right, so parameters table should have reference to the  Component tables

    And by join SystemImageReference with  Component on imagereference and then join Component with parameters  on componentid you should able to get all the data to the unique customer


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 18, 2018 5:57 AM
    Moderator
  • if you want to have maximum flexibility design should be something like this

    SystemImageReference table represent the list of SystemImage like : Server2003, SQL server, Linux, Windows10
    
    Components Table represent a list of components that cold be used by Any SystemImage reference
    
    Parameters Table represents a set of default parameters used by the SystemReferenceImage
    
    Bridge table linking SystemImageReference to Components and Parameters - This will indicate which components were part of image and what parameters are set for it. ImageID, ComponentID and ParameterID will be added in this table as FKs to point to the corresponds reference tables. Also there will be multipl entries per SystemImage based on number of Components and Parameters involved
    
    
    ClientImage table- this will have ClientID and ImageID as FKs pointing to Client table and SystemImageReference table


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 18, 2018 6:31 AM
    Moderator
  • I do not understand this line :
    ClientImage table- this will have ClientID and ImageID as FKs pointing to Clienttable and SystemImageReference table

     What is client table ? I have only ClientImage whihs from whish I should be able to get customer configuration from ImageReference, component and parameters

    How can I get thos information from your sample ?

    A systemReference table have a predefined set of Coponents, then for a single customer machine identifided as ClientImage table then I should be able to know for a pqrticulqr ClientImageID which SystemReferenceId has been used and which list of component which have been implemented ( list of implemented component can be less that the set of available component of a sytemImageReference.

    So canno see from your sample how can I get  the list of selected component and parameters for a referenceImage attached to a ClientImage ID

    I guess something is missing no ?

    thanks for update

    • Edited by wakefun Wednesday, July 18, 2018 7:14 AM
    Wednesday, July 18, 2018 7:04 AM
  • @Visakh16

    I do not understand this line :
    ClientImage table- this will have ClientID and ImageID as FKs pointing to Clienttable and SystemImageReference table

     What is client table ? I have only ClientImage whihs from whish I should be able to get customer configuration from ImageReference, component and parameters

    How can I get thos information from your sample ?

    Client table will be the table which holds details of customers for which the images were allocated 

    I just used the naming as clients. you can use customers or users instead :)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 18, 2018 7:11 AM
    Moderator
  • What you call Client table is in fact the table I named ClientImage, this from that table where I should be able to get all other information

    so how can I get from the CLientImage all information which has been used by selecting a referecenImageID and a set of component( not all) from component table ?

    regards

    Wednesday, July 18, 2018 9:27 AM