Variable number of entries in a field

[FredoC]

Hello, I am trying to make a nice database for my board game collection, with the name, publisher, year published, # of players etc... One of the entry will be the designer. Many games have just one, but some have 2, or more. I could use a Multivalued field, but I read it is discouraged by database normalization principles. From what I read, having a subform with many-to-many relationships would be the way to go, but I am not exactly sure about how to implement in my case, even though I feel like it is a fairly simple (or not too uncommon) situation. Anyone can help?



Frederic

[June7]

Yes, multi-value field is not preferred by experienced programmers but if you fully understand its drawbacks and how to deal with, it might be appropriate for your needs. Otherwise, have a related table where each selected developer is a record (this is actually what MVF does but table is hidden). Many-to-many actually involves 3 tables. Form arrangement would be main form bound to Games table and a subform bound to GameDevelopers table with a combobox for selection of developer from Developers table.

[davegri]

Have a look here.

Many-To-Many, two ways to update Junction table, includes Not In List handling

[CJ_London]

A multi value field still has the many to many table, it’s just that it is hidden and limited in structure- basically just containing the PK of the parent and the PK of the child.

On many occasions you want more than that - for example the date a relationship started or ended (employer/employee, customer/contact), or perhaps a quantity or type of relationship such as job role.

[madpiet]

Hello, I am trying to make a nice database for my board game collection, with the name, publisher, year published, # of players etc... One of the entry will be the designer.

BoardGame(GameID autonumber PRIMARY KEY, Game Title TEXT(25) NOT NULL, Publisher TEXT(50) NOT NULL, YearPublished INT NOT NULL)

Designer(DesignerID autonumber PRIMARY KEY, FirstName TEXT(25) NOT NULL, LastName TEXT(25) NOT NULL)

GameDesigner(DesignerID INT NOT NULL, GameID INT NOT NULL);

It's actually easier to do this in T-SQL because you can name and specify the foreign keys more cleanly.

CREATE TABLE GameDesigner (
DesignerID INT,
GameID INT,
CONSTRAINT pkGameDesigner PRIMARY KEY (DesignerID, GameID),
fkDesigner FOREIGN KEY DesignerID REFERENCES Designer(DesignerID),
fkGame FOREIGN KEY GameID REFERENCES Game(GameID)
);

[Ken Sheridan]

The attached file is really designed to illustrate the use of correlated combo boxes, but it happens to include a form in which a subform based on a table which models a many to many relationship type is embedded. As you'll see the subform allows multiple employers per contact to be entered by selecting the employers in a combo box. The combo box is bound to the EmployerID foreign key column, but is set up so that an employer can be selected by name. You would do the same for game designers.

[FredoC]

Thank you all for the responses. I realize I might not be knowledgeable enough about Access to implement your tips into what I am trying to do. See the form I had done for my coin collection, where I would enter the data for any new coin added. My game collection database would be similar, but keeping that coin form as an example, let's say some coins may have more than one Reverse artist (most times 1, but sometimes 2, and some others 3). So the field would have a variable number of values (names), and I would want to be able to enter any numbers of artist for that one label in the form, while keeping the database integrity where I could do a query to search for any coin from that artist, no matter if he/she contributed alone or with others. Any help? Do I make sense?

[ArviLaanemets]

Let's assume you'd have a situation where some coins may have several different images on reverse side:
Then you'd need the coins table like
tCoins: CoinID, CoinName, ...;
but without the field for reverse side,
and the table like
tCoinReverses: CoinRevID, CoinID, RevImage.

Then you create a continuous form based on table of coin reverses, which always displays a single image - but you can select any of them (from scrollbar, or from move buttons on footer). The form contains controls for CoinRevID and CoinID too, but their widths are set to 0, and they aren't visible. And you may add a button on header of form to add a new reverse too.

On your coins form, instead of field for displaying the reverse image, you place this additional form as subform (Simply dragging the continuous form into main form in design mode - the main form and subform must be linked by CoinID).

When you select a new coin in main form, the first of reverses for this coin is displayed in subform. You can select any of other reverses registered for this coin to be displayed instead, when they exist. Or you can add a new image for new reverse into subform, which will automatically linked with current coin activated in main form. The number of currently registered reverses for currently active coin is displayed at footer of subform

[jojowhite]

you create a separate table for ReverseArtists (see demo).
open MainFormCoins and enter Info on main form and on the sub-form.

[Welshgasman]

I would have the artists all in one table, but a junction table with a field to indicate whether Obverse or Reverse.
Then with your form as it is, make the controls combos, unless you wanted to see more than one at a time, then perhaps a listbox. Both could auto expand when they get the focus?