Database structure - lottenoorlander/je-shop GitHub Wiki

Items

name	type	options
id	uuid
name	char
price	int
weight	dec
description	text
year	dec
condition	char	A - B - C - D
Tag*

Tags

name	type
id	uuid
Idol*
ItemType*

Idols

name	type
id	uuid
name	char
Group*

Groups

name	type
id	uuid
name	nvarchar

ItemTypes

name	type
id	uuid
name	char

Sales

name	type
id	uuid
email address	nvarchar
shipping address	varchar
total cost	dec
isCompleted	boolean
Problem*
Item*

Shippings

name	type
id	uuid
tracking code	varchar
sent_on	date
arrived_on	dat
Sale*

Payments

name	type
id	uuid
paid_on	date
PaymentReference*
Sale*

PaymentReferences

name	type	options
id	uuid
type	char	Stripe - Paypal
reference	uuid

This needs to be filled in further when I know more about what Stripe and Paypal need to be stored. May need a slightly different structure or different tables depending on which of the two is used

Problems

name	type
id	uuid
description	text
solution	text
occured_at	date
solved_on	date
ProblemType*

ProblemTypes

name	type
id	uuid
name	char

Reservations

name	type
id	uuid
updated_at	date
Item*

can be cleaned with a script every hour for updated_at > 1h

Foreign keys

name	type	relation	tables
Sale	uuid	many to one	items ⇾ sale
Tags	uuid	many to many	tags ⇾ items
Idols	uuid	many to many	idols ⇾ groups
ItemType	uuid	many to one	tags ⇾ itemType
Reservation	uuid	many to one	items ⇾ reservation
Sale	uuid	many to one	problems ⇾ sale
ProblemType	uuid	many to one	problemType ⇾ problem