Looking for Suggestions on how to Organizing Product Options across Multiple Product Lines.

July 11, 20205 yr

I've got a list of Platforms and a list of Attributes.

Platforms are straight up by a unique ID. The amount of common Platforms is about 15, but the total possibilities are in the Mid 3 digit range.

Platforms
ID		Name
1		Apple II
2		Apple ///
3		Macintosh
4		Atari 2600
5		Amiga
6		Commodore 64
7		Windows
8		Amstrad CPC
9
...
130		...
131		Emerson Arcadia 2000
132		Bally Astrocade
...

The attributes are broken down by sub-categories (SubID) and a unique ID

Attributes
SubID		ID		SubID_Name			Name
1		391		Sound Devices Supported		Mockingboard Sound I
1		270		Sound Devices Supported		Roland 32
1		27		Sound Devices Supported		AdLib Music Synthesizer 
6		23		Input Devices Supported		Keyboard
6		24		Input Devices Supported		Mouse
11		254		Minimum CPU Class Required	Amiga 1000
11		349		Minimum CPU Class Required	6502 / Apple II+
11		350		Minimum CPU Class Required	65C02 / Apple //e
16		113		Business Model			Shareware
16		114		Business Model			Commercial
16		124		Business Model			Freeware / Free-to-play / Public Domain 
21		325		Media Type			Cassette Tape
21		237		Media Type			ROM Cartridge
21		516		Media Type			3.5" Floppy Disk
21		517		Media Type			5.25" Floppy Disk
21		737		Media Type			3" Floppy Disk
65		28		Controller Types Supported	Analog Joystick
65		41		Controller Types Supported	Digital Joystick
74		309		Players Supported		1 Player
74		310		Players Supported		1 - 2 Players
74		311		Players Supported		1 - 4 Players

In a conditional value list environment, I'd like to limit the choices above by Platform.

Each Platform will have choices from the Sub-Categories, and the individual ID can be for 1 or many Platforms

The part I'm working on here is how should I go about supporting any number of platforms (and to be able to add more later) in the value list?

Attributes
SubID		ID		SubID_Name			Name				Platforms_Supported
1		391		Sound Devices Supported		Mockingboard Sound I		1
1		270		Sound Devices Supported		Roland 32			3,7
1		27		Sound Devices Supported		AdLib Music Synthesizer		7 
6		23		Input Devices Supported		Keyboard			1,2,3,5,6,7,8
6		24		Input Devices Supported		Mouse				1,3,5,6,7
11		254		Minimum CPU Class Required	Amiga 1000			5
11		349		Minimum CPU Class Required	6502 / Apple II+		1
11		350		Minimum CPU Class Required	65C02 / Apple //e		1
16		113		Business Model			Shareware			1,2,3,5,6,7
16		114		Business Model			Commercial			1,2,3,4,5,6,7,8,9,...,130,131,132...
16		124		Business Model			Freeware			1,2,3,5,6,7
21		325		Media Type			Cassette Tape			1,4,6
21		237		Media Type			ROM Cartridge			4,6
21		516		Media Type			3.5" Floppy Disk		1,3,4,5,6,7
21		517		Media Type			5.25" Floppy Disk		1,2,6,7
21		737		Media Type			3" Floppy Disk			8
65		28		Controller Types Supported	Analog Joystick			1,2,7
65		41		Controller Types Supported	Digital Joystick		4,6,8,130,131,132...
74		309		Players Supported		1 Player			1,2,3,4,5,6,7...279 (All Platforms)
74		310		Players Supported		1 - 2 Players			1,2,3,4,5,6,7...279 (All Platforms)
74		311		Players Supported		1 - 4 Players			1,2,3,4,5,6,7...279 (All Platforms)

With the Sub-Category it's straight up, look for Devices by the SubID_Name. Each platform has a Media Type, Input Device option(s), Controller Type Option(s), etc..

But some of the Attributes are only for a specific Platform, while others are for many of them.

I've got the Conditional Value List thing worked out if I only have to deal with a single attribute / sub type at a time.

Show me all the Media Types
Show me all the Controller Types
Show me all the Sound Devices ..

The issue is:

Show me -only- the Media Types that are compatible with Platform X.

...and being able to add to those in the future, a new Platform and adding it to existing Attributes, and a new Attribute adding it to existing Platforms.

Most of the Attributes are limited to few Platforms, but some are limited to all but a few. A few Attributes would be compatible with -all- platforms.

The only way I'm seeing this being black and white simple, yes or no, is to have a field (column) for each platform, true of false. But then .. you add a new platform and you've got to go back and add potentially many of the Attributes, or add a new Attribute and then choose the Platforms for it.

Only the ID's are stored in a join table, the names are in a related table.

Should the platforms be another kind of relationship as well?