I want to create a User which has multiple Items but only one item of a specific type. The tables will look something like this.

1st table: item

2nd table: user

The User will select at the start an Item that he can only select as his first item. This means I could either create a OneToOne and a ManyToMany-Relationship like this:

1st table: item unchanged

2nd table: user

3rd table: user_normalitem

Or just create a ManyToMany-Relationship like this (Items that are firstItems are marked bold):

1st table: item unchanged

2nd table: user unchanged

3rd table: user_item

The problem is that if I would now insert a new relationship to user_item like this:

user_item_addition

it would go through without any exception, error or warning.

My first question is: "Is this second approach with user_item possible or do I have to go for user_normalitem?" Secondary is more an opinion: "Would you instead go for the first approach to make sure that it is unique? Or are multiple Relationships (OneToOne and ManyToMany) between two tables just bad behaviour?

CodePudding user response：

If I understood you question correct, you are on a totally wrong path.

You have User and you have items and you have items picked by users. That means you need three tables.

User table has some columns like id, name etc…

Items have columns like id, name, etc…

Items picked by user have 3 attributes Userid (foreign key to User Id), itemid (foreign key to item id), isFirstItem (1 if item is picked as first item else 0)

To make sure that the first item is unique you have multiple options. As I would do it in application layer, you can also add a trigger or you can add a 4 table only for the first item with a primary key on user id so that only one item can be added there.

CodePudding user response：

From what I read, I understood that a user can have multiple items but I didn't quite understand if the same item can be owned by different users.

In the case where the same instance of an item can be owned by different users then you need a many-to-many relationship which will produce the following tables: Users, Items and the middle relationship that, just for the sake of this example, I will call UsersToItems. The UsersToItems will contain its own primary key and the two foreign keys pointing to Users and Items. In this case, if you need only a single instance of ownership between the Users and the Items then you can use the unique clause on the foreign keys to avoid repeated t-uples of ownership, like so:

CREATE TABLE Users (
    id int NOT NULL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE Items (
    id int NOT NULL PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    name VARCHAR(255)
);

CREATE TABLE UsersToItems(
    id int NOT NULL PRIMARY KEY,
    idExtUser int NOT NULL, 
    idExtItem int NOT NULL, 
    FOREIGN KEY (idExtUser) REFERENCES Users(id),
    FOREIGN KEY (idExtItem) REFERENCES Items(id),
    UNIQUE(idExtUser, idExtItem)
);

In the case where an item can be owned only by one User then you need a one-to-many relationship where the Items table will possess its own primary key and a foreign key to the Users table. So, if you want to achieve uniqueness for this case too, you need to set as unique the pair 'item type' and 'the foreign key to the Users'.

CREATE TABLE Users (
    id int NOT NULL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE Items (
    id int NOT NULL PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    name VARCHAR(255),
    idExtUser int NOT NULL, 
    FOREIGN KEY (idExtUser) REFERENCES Users(id),
    UNIQUE(type, idExtUser)
);

Instead, if you simply need to have an Items table that allows only different types of items regardless of its relationships then apply the unique clause only to the 'type' column.