Tables restriction (1 Viewer)

jaryszek

Registered User.
Local time
Today, 01:48
Joined
Aug 25, 2016
Messages
756
hi Guys,

i have relationships like here:



full description of model you can find here:

https://www.access-programmers.co.uk/forums/showthread.php?t=304008

I have to change a little approach and need your help.

And now i have 2 tables: restrictedTable1 and restrictedTable2.
In each table you can see for which systemversion and topology i should restrict another systemversion for specific topology.

I created query to show what i want to:



so as you can see there are TopologyName and specific systemVersions for each of them.

And now each systemVersion specific for TopologyName should restrict usage of systemVersion characteristic for specififc Topology Name.
So systemVersion can restrict other SystemVersions based on restrictedTables dependencies.
Example:
In RestrictedTable2 there is topologyName FE in first column which should restrict Topology NW with specific SystemVersions,
So "SAP S/4HANA 1610 SP 01" for FE will restrict NW SystemVersions from table tblSystemVersion for only one choice: "SAP S/4HANA 1610 SP 01".

How can i appropriete restrict table tblSystemVersions using specific Topology Names and System Versions?

I thought to create one new table where i would put all dependencies.
But i do not know how to solve this technically.

Please help,
Best Wishes,
Jacek
 

Attachments

  • Screenshot_5.jpg
    Screenshot_5.jpg
    59.1 KB · Views: 271
  • Screenshot_7.png
    Screenshot_7.png
    76.5 KB · Views: 262
  • Database1611.accdb
    572 KB · Views: 80

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:48
Joined
May 21, 2018
Messages
8,463
Those restrictions tables do not look normalized. You have version names as columns. Why can you not simply have a single table?

TblAvailableVersions
Code:
  SystemVersionOne_FK
  SystemVersionTwo_Fk


For each SystemVersion1 you would list every possible SystemVersionTwo for each Topology. Since a system version has a topology as a related field. Once this table is complete you can check a topology and see the available system versions for each topology.
 

jaryszek

Registered User.
Local time
Today, 01:48
Joined
Aug 25, 2016
Messages
756
hi,

thank you!
Let me understand this.
I created new table TblAvailableVersions with these fields.
How can i relate them with present tables?

Those restrictions tables do not look normalized. You have version names as columns.

agree, i can transform these tables to something like:



For each SystemVersion1 you would list every possible SystemVersionTwo for each Topology.
I do not catch it yet, how can i add into tblAvailableVersion and how can i relete this table into present model?

Please give more details,
idea is awesome!

Best wishes,
Jacek
 

Attachments

  • Screenshot_8.png
    Screenshot_8.png
    88.3 KB · Views: 249
  • Database1611.accdb
    600 KB · Views: 89

jaryszek

Registered User.
Local time
Today, 01:48
Joined
Aug 25, 2016
Messages
756
Ok, i deleted Topology Field from tblSystemVersions table because we have topologies already in separate table tblTopology and with many to many relationship for customerTopology table.

Sorry for confusion,
Best,
Jacek
 

Attachments

  • Database1611.accdb
    596 KB · Views: 88

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:48
Joined
May 21, 2018
Messages
8,463
You are not using your PKs as you should. In my mind based on your descriptions the available version table is simply for each SV the related SVs in each topology.

Code:
SystemVersionOne_FK	SystemVersionTwo_FK
14	1
14	2
14	3
14	4
14	5
14	6
14	7
14	8
14	11
14	12
15	1
15	2
15	3
15	4
15	5
15	6
15	7
15	8
15	9
15	11
15	12
16	1
16	2
16	3
16	4
16	5
16	6
16	7
16	8
16	9
16	10
16	11
16	12
16	13
2	11
4	12
10	13
11	2
12	4
13	10



Since the topology ID is in the system version relationship I can bring this all together in a query as needed.
Code:
SELECT SystemVersionOne.topology, 
       SystemVersionOne.systemversion, 
       tblavailableversions.systemversionone_fk, 
       SystemVersionTwo.topology, 
       SystemVersionTwo.systemversion, 
       tblavailableversions.systemversiontwo_fk 
FROM   (tblsystemversions AS SystemVersionOne 
        INNER JOIN tblavailableversions 
                ON SystemVersionOne.systemversionid = 
                   tblavailableversions.systemversionone_fk) 
       INNER JOIN tblsystemversions AS SystemVersionTwo 
               ON tblavailableversions.systemversiontwo_fk = 
                  SystemVersionTwo.systemversionid;


Code:
SystemVersionOne.Topology	SystemVersionOne.SystemVersion	SystemVersionOne_FK	SystemVersionTwo.Topology	SystemVersionTwo.SystemVersion	SystemVersionTwo_FK
HANA	HANA 1.0 SPS12	14	NW	Netweaver 7.5 SP 01	1
HANA	HANA 1.0 SPS12	14	NW	SAP S/4HANA 1610 SP 01	2
HANA	HANA 1.0 SPS12	14	NW	SAP CRM 7.0 EHP4	3
HANA	HANA 1.0 SPS12	14	NW	SAP S/4HANA 1709	4
HANA	HANA 1.0 SPS12	14	NW	SAP ERP 6.0 EHP8	5
HANA	HANA 1.0 SPS12	14	NW	SAP SCM 7.0 EHP4	6
HANA	HANA 1.0 SPS12	14	NW	SAP SRM 7.0 EHP4	7
HANA	HANA 1.0 SPS12	14	NW	Netweaver 7.5 SP 13	8
HANA	HANA 1.0 SPS12	14	FE	SAP S/4HANA 1610 SP 01	11
HANA	HANA 1.0 SPS12	14	FE	SAP S/4HANA 1709	12
HANA	HANA 2.0 SPS2	15	NW	Netweaver 7.5 SP 01	1
HANA	HANA 2.0 SPS2	15	NW	SAP S/4HANA 1610 SP 01	2
HANA	HANA 2.0 SPS2	15	NW	SAP CRM 7.0 EHP4	3
HANA	HANA 2.0 SPS2	15	NW	SAP S/4HANA 1709	4
HANA	HANA 2.0 SPS2	15	NW	SAP ERP 6.0 EHP8	5
HANA	HANA 2.0 SPS2	15	NW	SAP SCM 7.0 EHP4	6
HANA	HANA 2.0 SPS2	15	NW	SAP SRM 7.0 EHP4	7
HANA	HANA 2.0 SPS2	15	NW	Netweaver 7.5 SP 13	8
HANA	HANA 2.0 SPS2	15	NW	Netweaver 7.52	9
HANA	HANA 2.0 SPS2	15	FE	SAP S/4HANA 1610 SP 01	11
HANA	HANA 2.0 SPS2	15	FE	SAP S/4HANA 1709	12
HANA	HANA 2.0 SPS3	16	NW	Netweaver 7.5 SP 01	1
HANA	HANA 2.0 SPS3	16	NW	SAP S/4HANA 1610 SP 01	2
HANA	HANA 2.0 SPS3	16	NW	SAP CRM 7.0 EHP4	3
HANA	HANA 2.0 SPS3	16	NW	SAP S/4HANA 1709	4
HANA	HANA 2.0 SPS3	16	NW	SAP ERP 6.0 EHP8	5
HANA	HANA 2.0 SPS3	16	NW	SAP SCM 7.0 EHP4	6
HANA	HANA 2.0 SPS3	16	NW	SAP SRM 7.0 EHP4	7
HANA	HANA 2.0 SPS3	16	NW	Netweaver 7.5 SP 13	8
HANA	HANA 2.0 SPS3	16	NW	Netweaver 7.52	9
HANA	HANA 2.0 SPS3	16	NW	SAP S/4HANA 1809	10
HANA	HANA 2.0 SPS3	16	FE	SAP S/4HANA 1610 SP 01	11
HANA	HANA 2.0 SPS3	16	FE	SAP S/4HANA 1709	12
HANA	HANA 2.0 SPS3	16	FE	SAP S/4HANA 1809	13
NW	SAP S/4HANA 1610 SP 01	2	FE	SAP S/4HANA 1610 SP 01	11
NW	SAP S/4HANA 1709	4	FE	SAP S/4HANA 1709	12
NW	SAP S/4HANA 1809	10	FE	SAP S/4HANA 1809	13
FE	SAP S/4HANA 1610 SP 01	11	NW	SAP S/4HANA 1610 SP 01	2
FE	SAP S/4HANA 1709	12	NW	SAP S/4HANA 1709	4
FE	SAP S/4HANA 1809	13	NW	SAP S/4HANA 1809	10

So with table tblAvailableVersions you would have all restrictions.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:48
Joined
May 21, 2018
Messages
8,463
Code:
i deleted Topology Field from tblSystemVersions

I posted before seeing this. So you cannot pull the Topology in a query. Your tblAvailableVersions should then contain the topology

tblAvailableVersion
TopologyOneID_FK
SystemVersionOne_FK
TopologyTwoID_FK
SystemVersionTwo_FK

your tblAvailableVerions looks like

Code:
TopologyOne_FK	SystemVersionOne_FK	TopologyTwo_FK	SystemVersionTwo_FK
1	14	1	1
1	14	1	2
1	14	1	3
1	14	1	4
1	14	1	5
1	14	1	6
1	14	1	7
1	14	1	8
1	14	1	11
1	14	1	12
1	15	1	1
1	15	1	2
1	15	1	3
1	15	1	4
1	15	1	5
1	15	1	6
1	15	1	7
1	15	1	8
1	15	1	9
1	15	1	11
1	15	1	12
1	16	1	1
1	16	1	2
1	16	1	3
1	16	1	4
1	16	1	5
1	16	1	6
1	16	1	7
1	16	1	8
1	16	1	9
1	16	1	10
1	16	1	11
1	16	1	12
1	16	1	13
2	11	2	2
2	12	2	4
2	13	2	10
3	2	3	11
3	4	3	12
3	10	3	13

this shows all restrictions for all topology. You may need to duplicate the records because I assume the restrictions go both ways. Depending on what topology is selected first. (This may or may not be the case)

In other words if you have
HANA HANA 1.0 SPS12 14 NW Netweaver 7.5 SP 01 1
you may want the record
NW Netweaver 7.5 SP 01 1 HANA HANA 1.0 SPS12
 

jaryszek

Registered User.
Local time
Today, 01:48
Joined
Aug 25, 2016
Messages
756
Hi MajP,

thank you! Awesome explanation! So the tblAvailableVersion you would create as separate, not related table?

And when i want to use values i can simple query and get whatever i want.

Best,
Jacek
 

jaryszek

Registered User.
Local time
Today, 01:48
Joined
Aug 25, 2016
Messages
756
Ok,

i created separate table and now i am trying to transform imported restrcitedtables from textes to numbers as foreign keys.



So i need the process of matching string fields with appropriate numbers.




How to combine this appropriately?
I tried do the same for Topology2 and VersionName2 but after joining queries my results were multiplied...

Please help,
Best wishes,
Jacek
 

Attachments

  • Screenshot_10.jpg
    Screenshot_10.jpg
    88.4 KB · Views: 189
  • Screenshot_11.png
    Screenshot_11.png
    95.5 KB · Views: 199
  • Screenshot_12.png
    Screenshot_12.png
    64.5 KB · Views: 191
  • Database1611.accdb
    612 KB · Views: 80

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:48
Joined
May 21, 2018
Messages
8,463
Sorry I made a mistake in the table posted. Where it lists topologyTwo_FK it shows 1 where it should show 3 (NW). Using your two restriction tables I would get the following.

TblAvailableVersions
Code:
TopologyOne_FK	SystemVersionOne_FK	TopologyTwo_FK	SystemVersionTwo_FK
1	14	3	1
1	14	3	3
1	14	3	4
1	14	3	5
1	14	3	6
1	14	3	8
1	14	3	11
1	15	3	1
1	15	3	3
1	15	3	4
1	15	3	5
1	15	3	6
1	15	3	8
1	15	3	9
1	15	3	11
1	16	3	1
1	16	3	3
1	16	3	4
1	16	3	5
1	16	3	6
1	16	3	8
1	16	3	9
1	16	3	10
1	16	3	11
2	4	3	4
2	10	3	10
2	11	3	11

Now this first block shows what NW versions are available when you have Hana topology and HANA 1.0 SPS12.

I will assume you need the reflection of this. So if you have NW topology to start and you want to know the Hana versions. If that is the case you need the reflection. Your table would then look like

Code:
TopologyOne_FK	SystemVersionOne_FK	TopologyTwo_FK	SystemVersionTwo_FK
1	14	3	1
1	14	3	3
1	14	3	4
1	14	3	5
1	14	3	6
1	14	3	8
1	14	3	11
1	15	3	1
1	15	3	3
1	15	3	4
1	15	3	5
1	15	3	6
1	15	3	8
1	15	3	9
1	15	3	11
1	16	3	1
1	16	3	3
1	16	3	4
1	16	3	5
1	16	3	6
1	16	3	8
1	16	3	9
1	16	3	10
1	16	3	11
2	4	3	4
2	10	3	10
2	11	3	11
3	1	1	14
3	3	1	14
3	4	1	14
3	5	1	14
3	6	1	14
3	8	1	14
3	11	1	14
3	1	1	15
3	3	1	15
3	4	1	15
3	5	1	15
3	6	1	15
3	8	1	15
3	9	1	15
3	11	1	15
3	1	1	16
3	3	1	16
3	4	1	16
3	5	1	16
3	6	1	16
3	8	1	16
3	9	1	16
3	10	1	16
3	11	1	16
3	4	2	4
3	10	2	10
3	11	2	11

You do want to make a composite index on this table where you cannot duplicate a record with the same 4 fields. To see all the system topology data you just build a query by adding the topology table and the system table in twice. You can make a relation to enforce referential integrity, but it would be its own "island" showing the relations to the topology and system tables, but not connected to your other relationships.
 

jaryszek

Registered User.
Local time
Today, 01:48
Joined
Aug 25, 2016
Messages
756
hi MajP,

thank you, i will
how can i create relation here? i do not have even key which i can combined with this own island :)

Best,
Jacek
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:48
Joined
May 21, 2018
Messages
8,463
how can i create relation here? i do not have even key which i can combined with this own island

Take tblAvailableVersions and put in the middle. Add two copies of tblTopology one on the left side and one on the right side. Add two copies of tblSystem versions one on the left and one on the right. Connect left side tables to topologyOne_Fk, and systemVersionOne_FK. The right side connect to topolgytwo and systemTwo. You can then enforce referential integrity so that you do not get values in tblAvailableVersions that do not exist in the primary tables.
 

jaryszek

Registered User.
Local time
Today, 01:48
Joined
Aug 25, 2016
Messages
756
oo perfect, thank you very much. i am learning every day something new :)

Best,
Jacek
 

jaryszek

Registered User.
Local time
Today, 01:48
Joined
Aug 25, 2016
Messages
756
Hi,

i have one more question regarding this:

how should i create index in tblAvailableVersions?
I should have PK and separate index called (i do not know what name conventions should i use for indexing in Access) IndexComposite for 4 separate fields?

Best,
Jacek
 

Users who are viewing this thread

Top Bottom