E-shop schema for postgresql

1. country Table:

   • This table stores information about countries.
   • It has a primary key id, a name field to store the country name, and various timestamp fields (created_at and updated_at) to track the creation and modification times of records.
   • Additionally, it has fields for sorting order ("order"), whether the country is published (is_published), and whether it is active (is_active).

2. vat Table:

   • This table stores information about Value Added Tax (VAT) rates for different products or services.
   • It has a primary key id, a name field to describe the VAT, a vat field to store the VAT rate, and timestamp fields (created_at and updated_at) for record management.
   • Similar to the country table, it also has fields for sorting order, publication status, and activity status.

3. countryvat Table:

   • This is an intermediate table used to establish a many-to-many relationship between countries and VAT rates.
   • It contains foreign keys country_id and vat_id that reference the id fields in the country and vat tables, respectively.
   • The primary key for this table is a combination of country_id and vat_id, making sure that each combination of country and VAT rate is unique.

4. customer Table:

   • This table stores information about customers.
   • It has a primary key id, a foreign key country_id that references the id field in the country table, a foreign key vat_id that references the id field in the vat table, and fields for the customer's name and timestamps.
   • Like other tables, it also has fields for sorting order, publication status, and activity status.

5. customeraddress Table:

   • This table stores the addresses of customers.
   • It has a primary key id, a foreign key customer_id that references the id field in the customer table, an address field to store the address information, and timestamps.
   • As usual, it has fields for sorting order, publication status, and activity status.

6. banner Table:

   • This table stores information about banners, which are typically used for advertising.
   • It has a primary key id, a location field to indicate where the banner is displayed, fields for a code, a url, and an banner_image to represent the banner, and timestamps.
   • Also, fields for sorting order, publication status, and activity status are included.

7. bannerstatistic Table:

   • This table tracks statistics for banner views.
   • It has a primary key id, a foreign key banner_id that references the id field in the banner table, a session_id field to identify the session during which the banner was viewed, and timestamps.

8. appointment Table:

   • This table stores information about appointments.
   • It has a primary key id, a description field for the appointment description, a start_at field to store the start time of the appointment, and timestamps.
   • As usual, fields for sorting order, publication status, and activity status are included.

9. pressrelease Table:

   • This table stores information about press releases.
   • It has a primary key id, a description field for the press release content, a filepath field to store the file path of the press release (if any), and timestamps.
   • Additionally, fields for sorting order, publication status, and activity status are included.

10. media Table:

    • This table stores information about media files.
    • It has a primary key id, a media_path field to store the path of the media file, and timestamps.
    • Similar to other tables, fields for sorting order, publication status, and activity status are included.

11. event Table:

    • This table stores information about events.
    • It has a primary key id, a name field to store the event name, a description field for the event details, start_at and end_at fields to indicate the event's start and end times, and timestamps.
    • Also, fields for sorting order, publication status, and activity status are included.

12. eventmedia Table:

    • This table establishes a many-to-many relationship between events and media files.
    • It contains foreign keys event_id and media_id that reference the id fields in the event and media tables, respectively.
    • The primary key for this table is a combination of event_id and media_id, ensuring uniqueness of event-media associations.

13. supplier Table:

    • This table stores information about suppliers.
    • It has a primary key id, a name field to store the supplier's name, and timestamps.
    • As usual, fields for sorting order, publication status, and activity status are included.

14. tag Table:

    • This table stores information about tags.
    • It has a primary key id, a name field to store the tag name, and timestamps.
    • Similarly, fields for sorting order, publication status, and activity status are included.

15. brand Table:

    • This table stores information about brands.
    • It has a primary key id, a name field for the brand name, a logo_image field to store the path of the brand's logo image (if any), and timestamps.
    • Additionally, fields for sorting order, publication status, and activity status are included.

16. supplierbrand Table:

    • This table establishes a many-to-many relationship between suppliers and brands.
    • It contains foreign keys brand_id and supplier_id that reference the id fields in the brand and supplier tables, respectively.
    • The primary key for this table is a combination of brand_id and supplier_id, ensuring uniqueness of brand-supplier associations.

17. category Table:

    • This table stores information about categories.
    • It has a primary key id, a hero_image field to store the path of the category's hero image (if any), a name field for the category name, and timestamps.
    • Also, fields for sorting order, publication status, and activity status are included.

18. relatedcategory Table:

    • This table establishes a many-to-many relationship between categories.
    • It contains foreign keys from_category_id and to_category_id that reference the id fields in the category table.
    • The primary key for this table is a combination of from_category_id and to_category_id, ensuring uniqueness of category-category associations.

19. feature Table:

    • This table stores information about product features.
    • It has a primary key id, a name field to store the feature name, and timestamps.
    • As usual, fields for sorting order, publication status, and activity status are included.

20. featureattribute Table:

    • This table stores information about attributes related to features.
    • It has a primary key id, a foreign key feature_id that references the id field in the feature table, a name field for the attribute name, and timestamps.

21. categoryfeature Table:

    • This table establishes a many-to-many relationship between categories and features.
    • It contains foreign keys feature_id and category_id that reference the id fields in the feature and category tables, respectively.
    • The primary key for this table is a combination of feature_id and category_id, ensuring uniqueness of feature-category associations.

22. product Table:

    • This table stores information about products available in the online store.
    • It has a primary key id, a foreign key brand_id that references the id field in the brand table (indicating the product's brand), a name field for the product name, a hero_image field to store the path of the product's hero image (if any), and timestamps.
    • As usual, fields for sorting order, publication status, and activity status are included.

23. relatedproduct Table:

    • This table establishes a many-to-many relationship between products.
    • It contains foreign keys from_product_id and to_product_id that reference the id field in the product table.
    • The primary key for this table is a combination of from_product_id and to_product_id, ensuring uniqueness of product-product associations.

24. productsupplier Table:

    • This table establishes a many-to-many relationship between products and suppliers.
    • It contains foreign keys product_id and supplier_id that reference the id fields in the product and supplier tables, respectively.
    • The primary key for this table is a combination of product_id and supplier_id, ensuring uniqueness of product-supplier associations.

25. productprice Table:

    • This table stores pricing information for products based on various criteria such as country, VAT rate, and supplier.
    • It contains foreign keys product_id, vat_id, country_id, and supplier_id that reference the corresponding tables (product, vat, country, and supplier), representing the specific product's price in a given country, with a particular VAT rate, from a specific supplier.
    • The table also contains a price field to store the price of the product, a is_default field to indicate if this is the default price for the product, and timestamps.

26. productcustomerprice Table:

    • This table stores pricing information for products based on various criteria such as country, VAT rate, and customer.
    • It contains foreign keys product_id, vat_id, country_id, and customer_id that reference the corresponding tables (product, vat, country, and customer), representing the specific product's price for a given customer in a particular country, with a specific VAT rate.
    • The table also contains a price field to store the price of the product for the customer, a is_default field to indicate if this is the default price for the product-customer combination, and timestamps.

27. productcategory Table:

    • This table establishes a many-to-many relationship between products and categories.
    • It contains foreign keys product_id and category_id that reference the id fields in the product and category tables, respectively.
    • The primary key for this table is a combination of product_id and category_id, ensuring uniqueness of product-category associations.

28. producttag Table:

    • This table establishes a many-to-many relationship between products and tags.
    • It contains foreign keys product_id and tag_id that reference the id fields in the product and tag tables, respectively.
    • The primary key for this table is a combination of product_id and tag_id, ensuring uniqueness of product-tag associations.

29. productmedia Table:

    • This table establishes a many-to-many relationship between products and media files.
    • It contains foreign keys product_id and media_id that reference the id fields in the product and media tables, respectively.
    • The primary key for this table is a combination of product_id and media_id, ensuring uniqueness of product-media associations.

30. productattribute Table:

    • This table stores information about product attributes.
    • It contains foreign keys product_id, feature_id, and featureattribute_id that reference the corresponding tables (product, feature, and featureattribute).
    • The primary key for this table is a combination of product_id, feature_id, and featureattribute_id, ensuring uniqueness of product-attribute associations.

31. offer Table:

    • This table stores information about offers or promotions available in the online store.
    • It has a primary key id, a name field to describe the offer, a description field for additional details, start_at and end_at fields to specify the offer's duration, and timestamps.
    • As usual, fields for sorting order, publication status, and activity status are included.

32. offerdetail Table:

    • This table stores information about the details of offers and their associated products.
    • It contains foreign keys product_id and offer_id that reference the id fields in the product and offer tables, respectively.
    • The table also contains a price field to store the discounted price for the product during the offer, along with timestamps.

33. shoppingcartitem Table:

    • This table stores information about items in a shopping cart.
    • It has a primary key id, a foreign key product_id that references the id field in the product table (indicating the product in the cart), a quantity field to represent the number of products in the cart, a cart_id field to uniquely identify the shopping cart, and timestamps.

34. order Table:

    • This table stores information about orders placed by customers.
    • It has a primary key id, a total field to store the total amount of the order, a quantity field to represent the total quantity of products in the order, an email field to store the email address of the customer, and timestamps.

35. orderdetail Table:

    • This table stores information about the details of an order, including the products and quantities.
    • It contains foreign keys order_id and product_id that reference the id field in the order and product tables, respectively.
    • The table also contains a quantity field to represent the quantity of a product in the order and a price field to store the price of the product at the time of the order, along with timestamps.

36. This database schema models various entities and their relationships in an e-commerce or online store application. The relationships between different tables are established using foreign keys, and the primary keys ensure the uniqueness of records within each table. The timestamps (created_at and updated_at) help track the creation and modification times of records.

    Keep in mind that the schema provided here is just a starting point and may need further refinement based on specific application requirements and use cases. Additionally, the schema can be expanded or modified to accommodate new features and functionalities as the application evolves.

              
                create table country(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);


create table vat(
id serial primary key not null,
name varchar(200),
vat int not null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table countryvat(
country_id int references country(id),
vat_id int references vat(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(country_id, vat_id)
);

create table customer(
id serial primary key not null,
country_id int references country(id),
vat_id int references vat(id),
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table customeraddress(
id serial primary key not null,
customer_id int references customer(id),
address varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table banner(
id serial primary key not null,
location int not null default 1,
code text null,
url text null,
banner_image text null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table bannerstatistic(
id serial primary key not null,
banner_id int references banner(id),
session_id text null,
created_at timestamp not null default now()
);


create table appointment(
id serial primary key not null,
description text null,
start_at timestamp not null default now(),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table pressrelease(
id serial primary key not null,
description text null,
filepath text null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table media(
id serial primary key not null,
media_path text not null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);


create table event(
id serial primary key not null,
name varchar(200),
description text null,
start_at timestamp not null default now(),
end_at timestamp not null default now(),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table eventmedia(
event_id int references event(id),
media_id int references media(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(event_id, media_id)
);


create table supplier(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table tag(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);


create table brand(
id serial primary key not null,
name varchar(200),
logo_image text null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table supplierbrand(
brand_id int references brand(id),
supplier_id int references supplier(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(brand_id, supplier_id)
);


create table category(
id serial primary key not null,
hero_image text null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table relatedcategory(
from_category_id int references category(id),
to_category_id int references category(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(from_category_id, to_category_id)
);

create table feature(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);


create table featureattribute(
id serial primary key not null,
feature_id int references feature(id),
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);

create table categoryfeature(
feature_id int references feature(id),
category_id int references feature(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(feature_id, category_id)
);


create table product(
id serial primary key not null,
brand_id int null references brand(id),
name varchar(200),
hero_image text null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);


create table relatedproduct(
from_product_id int references product(id),
to_product_id int references product(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(from_product_id, to_product_id)
);


create table productsupplier(
product_id int references product(id),
supplier_id int references supplier(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, supplier_id)
);

create table productprice(
product_id int references product(id),
vat_id int references vat(id),
country_id int references country(id),
supplier_id int references supplier(id),
price NUMERIC(6, 4) not null default 0,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
is_default BOOLEAN NOT NULL default True,
PRIMARY KEY(product_id, country_id,vat_id, supplier_id)
);

create table productcustomerprice(
product_id int references product(id),
vat_id int references vat(id),
country_id int references country(id),
customer_id int references customer(id),
price NUMERIC(6, 4) not null default 0,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
is_default BOOLEAN NOT NULL default True,
PRIMARY KEY(product_id, country_id, vat_id, customer_id)
);


create table productcategory(
product_id int references product(id),
category_id int references category(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, category_id)
);

create table producttag(
product_id int references product(id),
tag_id int references tag(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, tag_id)
);

create table productmedia(
product_id int references product(id),
media_id int references media(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, media_id)
);

create table productattribute(
product_id int references product(id),
feature_id int references feature(id),
featureattribute_id int references featureattribute(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, feature_id, featureattribute_id)
);

create table offer(
id serial primary key not null,
name varchar(200),
description text null,
start_at timestamp not null default now(),
end_at timestamp not null default now(),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
"order" int not null default 0,
is_published BOOLEAN NOT NULL default False,
is_active BOOLEAN NOT NULL default True
);

create table offerdetail(
product_id int references product(id),
offer_id int references offer(id),
price NUMERIC(6, 4) not null default 0,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(product_id, offer_id)
);

create table shoppingcartitem(
id serial primary key not null,
product_id int references product(id),
quantity int not null,
cart_id text not null,
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);

create table "order"(
id serial primary key not null,
total NUMERIC(6, 4) not null,
quantity int not null,
email text not null,
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);


create table orderdetail(
order_id int references "order"(id),
product_id int references product(id),
quantity int not null,
price NUMERIC(6, 4) not null,
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(order_id, product_id)
);