Adding Derived Attribute To An Entity With Sqlite3

Question

Hello I have 2 entities that are CUSTOMER and PRODUCT at my Entity Relation Diagram(ER).

CUSTOMER and PRODUCT has a M to N relationship which is RATE and this relationship has 2 attributes which are Comment and Rate.

My PRODUCT entity has a derived attribute named Rating-avg which is the average rating of the product, being rated by the CUSTOMER's.

I don't know and can't find how to add the derived attribute to the table while creating it or altering it.

I would be really glad if someone could help.

I am using SQLite3(3.25.2) and SQLiteStudio(3.2.1) (The latest versions up to date.).

score 0 · Answer 1 · answered Oct 21 '18 at 11:50

You would use a third table, which is called a "junction" or "association" table:

create table CustomerProducts (
    CustomerProductId int primary key,
    CustomerId int references customers(CustomerId),
    ProductId int products(productId),
    Rate ?,  -- unclear what the type is
    Comment text
);

You could name the table Rate, if you like. I typically name association tables after the two tables involved in the relationship, unless it is an entity itself.

Adding Derived Attribute To An Entity With Sqlite3

1 Answers1