Hi, relative newb to db here. I use mysql, php, perl successfully on several small projects, all for in-house use only by myself and other software developers in my organization, for our own lab-rat purposes.

I'm having difficulty figuring out the best way to represent an arbitrary number of arbitrary-length lists. Actually, what I need are sets, not lists, since for my purpose order is insignificant. Thinking too much like a programmer again.

I have a number of systems (they're test systems in our lab). I have the schema for systems & components, no problems.

I want to allow any number of groups of systems. A system can be in any number of groups. Groups would be used for different teams (which systems are ours?) or different purposes (which systems are configured for feature X, which systems are semi-reserved for nightly automated regression tests, etc).

Here are options I've considered:

1) A table for groups, keyed by group name, with N columns, sys1name, sys2name, sys3name, etc. Of course, this is awful; deleting the system in sys2name for a given group means shifting all the others (i.e., replacing the row), and I don't see a good way to search for groups containing system Y.

2) turn (1) on its side: for the table characterizing the set of systems, have columns Group1, Group2, etc. Ick. Clumsy to search for the systems in a group, and limits number of groups a system can be in.

3) For each group X, a table GroupX, with one field, system name. Again, no good way to search, and no good way to interate over groups. (Of course, I could have a table of group names, but I can't search N tables at once.)

4) Storing a lists as CSV text in a field. Allows searching, but requires an upper bound on the list (actually, text string) length in the field schema. Searchable, but seems clumsy.

5) Just thought of another way: have a group members table, with group name and system name columns. One row per group/member pair. No primary key. Hmm, this is probably the answer.

Scalability isn't really an issue, with a hundred or so systems. But I'd rather learn the right way to do it first.

The end result will be a web page listing all the groups (or a predefined set). The user clicks on a group name to bring up a page listing the systems in that set, along with high level pertinent data for each system, including links to delve deeper.

Thanks!

5) definitely sounds like the way forward.

Learjeff

If you want to have an unlimited and changing number of lists, and a changing length of each list; without duplication ... you do have to model it properly. Assuming you will implement in MySQL, not csv files.

[5] is the closest option you have identified to the correct model, but you do not appear to understand keys and Primary Keys. If the SystemCode is the PK in the System table, and the GroupCode is the PK in the Group table, then your [5] is an Associative Table (without repetition of either System or Group attributes). This is the ordinary resolution of the many-to-many relation between Group and System. However, it does have a Primary Key. The PK is the GroupCode+SystemCode, the pair as you call it; that prevents repetition of the Group-System pair. The columns above the line are the Primary Key. These are Foreign Keys to each of the Group and System tables.

In fact, from your post, Component and Feature are also many-to-many with System. If you do not wish to have the pair repeated, forming the PK from the two parents is the normal method. The database engine will prevent duplicates.

Have a look at this. In IDEF1X, the solid lines mean Identifying Relations (the parent PK is used to make up the child PK, strong relations); the dotted lines mean Non-Identifying Relations (the child PK is independent of the parent PK, weak relations). In either case the parent PK is migrated to the child as a Foreign Key, that is how the relation is established.

For your understanding, I have supplied Component such that there can be more than one unit in a System, but the fact of the Component in the System, is not repeated. Enforced by the server.

As a counterpoint, not suggesting it is true in reality, I have supplied Feature as repeatable for each System. Here, you will have to limit the number of times a Feature is entered against a System, in your code. Enforced by you.

Also, you appear to think that Keys are single column only. That is not correct. Compound keys are normal in a relational database.

Last, searching is not a problem in any of the above; if you think it is, post again.

Thanks, both, and that makes perfect sense Derek: combining the associated keys to provide a primary key for the association table to avoid duplicates.

In my case, a component belongs to exactly one system. Actually, it represents a slot or location in that system, as distinct from the piece of hardware in that slot or location, since the piece of hardware has its own serial number and can be moved between systems, whereas connections between systems stay even if the hardware is replaced ... blah blah blah ...

But useful to chew on; no doubt there'll be other things that map as you say. It helped to see your diagram and I appreciate the extra effort. It's always nice to see how a pro would approach the problem.

Thanks again!