SQL Database - Organize Database correctly over different tables-CodePudding

I want to create a Relational Database (mySQL-Workbench and MariaDB-Server), which organizes the inventory of an Online-Bookstore.

I already identified the different attributes each product (mostly books) will have and thought about organizing that database in at least three different tables 1. productInformation 2. marketInformation 3. stockInformation:

But maybe someone got a better idea?

id INT(10)
isbn10 VARCHAR(10)
isbn13 VARCHAR(13)
asin VARCHAR(10)
titel VARCHAR(300)
autors ? Question: A product may have 0 or 1 or 2 or X Authors, that will be different for each product, how can I handle that? As far as i know sql does not have an list or array datatype..
publisher VARCHAR(100)
edition INT(5)
productForm VARCHAR(100)
binding VARCHAR(100)
productHeight INT(5)
productWidth INT(5)
productWeight INT(5)
numberOfPages INT(5)
language VARCHAR(50)
mediafiles IMAGE

priceDE DECIMAL(7,2)
priceAT DECIMAL(7,2)
priceCH DECIMAL(7,2)
priceAMZused DECIMAL(7,2)
priceAMZnew DECIMAL(7,2)
priceM DECIMAL(7,2)
priceR DECIMAL(7,2)
fixedBookPrice BOOL
available BOOL
salesrank varchar(20)
NumberOfNewOffers INT(5)
NumberOfUsedOffers INT(5)

InventoryQuantity INT(5)
StorageLocation VARCHAR(50)
Sku VARCHAR(50)
Condition VARCHAR(50)
ConditionDescription VARCHAR(500)
CanBeSoldAsNew BOOL
Supplier VARCHAR(50)
Shipper VARCHAR(50)

Those attributes, let's call them salesHistory, can only be accessed through another database which organizes the orders of that bookstore. That leads me to a second question, should the orders be organized in a completely different database or would it be better to organize it in the same database with different tables? The orders will be accessed through the Amazon/eBay API.

NumberOfSalesNew INT(10)
NumberOfSalesUsed INT(10)
NumberOfSalesM INT(10)
NumberOfSalesR INT(10)

CodePudding user response：

You should store author's information in a separate table with the foreign key relationship to the first table.

SHEET 1: If edition, productform and binding are of fixed options then you can use enum in front end and save corresponding integer values in the table. But if those could be change in future you can use separate table for each of them with an integer primary key and then store the primary key in the table as foreign key reference.

If I got you right there is single only single record in all three tables. So you can consider single table instead of three tables. Please let me know if I got it wrong.