• Catalog the items in your inventory. Does not currently store the quantity of each item or any shipping/recieving issues.
• Stores and automatically tracks pricing information on all products in your catalog, including base-price, seasonal pricing, discounts, etc.
• Maintain information on all website users, including account information, credit cards, billing & shipping addresses, contact information, etc.
• This db is only designed for use in the USA (and the US Dollar). This is not designed to support international currency or international law. If you want to get it to work in a different country/currency, you are free to create a fork of this project.
• No 'group accounts', or in other words, it's designed for one account per person. If you peruse the code, you will notice that it does include some code that will be used to implement this in future releases.
• I'm not trained in law by any means. If this database this somehow doesn't meet USA federal/state laws/regulations on computer security, privacy, finance (such as the Sarbanes-Oxley Act), or any other federal, state, or local regulation or law, I am not responsible for your misuse of this code.
• Not currently designed for full inventory/shipping management: i.e., inventory levels, orders, and deliveries. It currently tracks which products are available as well as each transaction the product is purchased in.

• Stored functions and procedures, which are not implemented yet.
• Fully integrated Inventory Management tracking, including inventory levels, orders, sales, and deliveries.
• Support for tax-exempt entities, as well as 'group accounts' or other entities, rather than just 'individual' (single-person) accounts.
• tracking & managing product returns & refunds

as you can notice, any table ending in '_type' is a lookup table used to ensure valid input into the associated foreign-key-constrained column. Also, 'city', 'state', and 'postal_code' are basically lookup tables.

• system_user
  • A list of all database users who have permission to access this database.
  • This table is NOT updated automatically to reflect the current database user list, so your DBA must keep this table updated.
• system_user_type
  • A lookup table that determines the type of database user.
• account
  • Used to store essential account information and foreign keys for relational data (contact, credit_card, transaction, and address tables.
• account_type
  • A lookup table that determines the type of account.
  • A common example may be for you to store both customer and admin accounts in this database, and as a result, a foreign key reference to this table is used to indicate the type of account.
• contact
  • Represents all of the contact information for an individual account. Keeping contact and account tables separate will makes it easier to accommodate multi-user accounts in the future.
  • For now, there is a 1-to-1 relationship between contact and account.
• contact_type
  • A lookup table that determines the type of contact. This may not be necessary. It might be depricated for future versions, although This might be necessary in order to fully implement multi-user accounts.
• credit_card
  • Stores credit card information for each credit card.
  • Each account can have 1-to-many credit cards.
• credit_card_type
  • A lookup table that determines the type of credit card, such as Visa, MasterCard, DiscoverCard, etc.
• transaction
  • Represents a single transaction. Has a foreign key reference to account. An account may have 1-to-many transactions.
  • NOTE: the transaction.transaction_date column may seem redundant in light of the transaction.creation_date column, but that is not true. The transaction.creation_date column is used to track the date each row was created, while transaction.transaction_date tracks the date of the transaction. If the data type of a column in the table needs to be changed, for example, you might transfer the data to a temporary table, drop the table, and re-create the table with the new changes, in which case the value in the transaction.creation_date column will change, but the transaction.transaction_date column will not.
• transaction_type
  • A lookup table that determines the type of transaction.
  • This table may be depricated in the future. I'm including it as a prospective member, but i'm not sure it is necessary.
• transaction_item
  • An item that was purchased in a particular transaction.
  • Each transaction can have 1-to-many transaction-items.
• item
  • A inventory of all products available to be sold.
• item_category
  • Determines the category of any given item in the database inventory.
  • There is a one-to-one relationship between an item and its item_category
• item_subcategory
  • Same as item_category, just to define categories underneath categories in item_category.
  • There can be 1-to-many subcategories(in item_subcategory) per each category(in item_category).
• price
  • Store price information, including base price, sale prices, custom price duration, discounts, etc.
  • Capable of storing price information based on a time-frame, so you don't need to manually edit the price the day a special price goes live (or ends).
• price_type
  • A lookup table that determines the type of price.
  • Common examples include base price, seasonal discount price, holiday price, etc.
• address
  • Stores address information associated with accounts.
  • A user may have 1-to-many addresses per account.
  • This database will not constrain postal codes, cities, and states to ensure proper addresses. If a user enters the wrong address, either you can handle that ouside of the database or let the user suffer for their foolishness.
• address_type
  • A lookup table that defines the type of address. In this case, an address can either be a billing address or shipping address.
• city
  • A lookup table that defines the city for addresses.
  • If you want to create a version of this database which supports any country other than the USA, you will need to fork this project.
• state
  • A lookup table that defines the state for addresses.
  • If you want to create a version of this database which supports any country other than the USA, you will need to fork this project.
• postal_code
  • A lookup table that defines the postal code for addresses.

Feel free to contribute!

• Each Primary Key index starts at 1001. This way, each table has 1000 rows you can experiment with when debugging, altering database designs, etc.
• A column whose name ends in '_type' shall be a foreign key reference to the lookup table with the same name.
• Capitalize reserved words.
• commas before columns.
• indent using spaces.
• Each table has 4 columns used to track data entry:
  • created_by
  • creation_date
  • last_updated_by
  • last_update_date

⬆️[Back to Top]

Following is a list of contributors:

Be the first!

⬆️[Back to Top]

You can report the bugs at the issue tracker

⬆️[Back to Top]

Kyle Birch(@runninguru) under MIT License