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)
);