Portfolio Project: A Complete Database

Hello!

A made my first Complete Database with PostgreSQL, Postbird and Linux. I had some issues, but I managed to meet the requirements:

  1. Creating user to manage database.
  2. Creating tables with create_tables.sql.
  3. Exporting my newly created database
  4. Creating db diagram with dbdiagram.io.
  5. Updating database using created db diagram.
  6. Populating db with some data.
  7. Ensuring db with constraints to stay intact.
  8. I add indexes for search products by name, and indexes to check order status and product status.
  9. Finally I purged dead lines with VACUUM to maintain my db.

You can view my project at Github: https://github.com/scallopy/postgres_db_project

If you have suggestions for improving my first PostgreSQL db, please post them in the comments below.

Greetings!
Petya Velikova

Hi, there’s a foreign key in your categories table that references the categories primary key. I’m not sure how you were able to make this key because a foreign key is the primary key of another table. Your database diagram also says the primary key and parent id have a one-to-many relationship. Is this to create subcategories or a group of categories? If it is, then maybe the name of the key could be subcategory_id or category_group_id, but the key does not reference another table and does not seem like a foreign key.

Hi, do you visit my repo on github to view entire project and db diagrame? postgres_db_project/Copy of Sample Ecommerce.pdf at main · scallopy/postgres_db_project · GitHub

Yes, in table categories, there is a store, id, name and parent_id. This meaning that a category can have subcategories. I can tell you more about that . I was make this relations in a real project.

my email: petia.t.velikova@gmail.com

Yes, I looked at your sql, database diagram, and images of your database schema to see what your tables were like and I noticed the parent_id column in the categories table. It didn’t make sense to me because it was a foreign key of the same table.

I was wondering how you made this column a foreign key without another table and since you were able to make this key, did it save space in your database or make your database easier to maintain? Thanks for your reply

This is from my real project with Python and Django:

from mptt.models import MPTTModel, TreeForeignKey

class Category(MPTTModel):
    parent = TreeForeignKey('self', related_name='children', null=True, blank=True, on_delete=models.CASCADE)
    name = models.CharField(max_length=200)
    slug = models.SlugField(_('Slug'), unique=True, help_text=_("Slug will be generated automatically from the name of the product"))
    store = models.ForeignKey(Store, on_delete=models.CASCADE)

This works. it is posible to make a Foreign key from primary key. A can’t explain you. This is a way to make subcategories.

How does this method improve your database? Or did you just want to make the table this way? Thanks for replying





add_category_5

In my case this make a Category Tree. You can see my project:

With Computer Scence I understood how I did that. I was made my category structure in my project with help of stackoverflow.com, but now I can do that only with my knowlege.

Okay, I can see how you made your project. What application is your project being rendered on?

“did it save space in your database or make your database easier to maintain?”
This is a good! This is a very intresting question!
Yes, of course.

  1. Here we have one table for categories instead of tables of categories, sub_categories1, sub_categories_2, sub_categories3 and so on. This save space in the database.
  2. When we search for a product by category we don’t need to search for product’s category in many tables
    Screenshot from 2022-04-02 11-39-17
    category on a few of tables.

or
SELECT * FROM products WHERE products.category,name = “Hand Tools” mayby :thinking:

in my Python code:

def product_detail(request, pk, slug, category_slug=None, store_slug=None,):
    product = get_object_or_404(Product, pk=pk)
    category = product.category
    store = product.store

   #print(product.slug)
    return render(request, 'product/product_detail.html', {'product': product })

'''

This is my own project for e-commerce. It is not deployed yet. If you want see my project repo you must tell me to grand you permissions, because this project is private in github. I wrote my email address above. Thank you for your intresting and useful feedback. :seedling:

So for your database project you just kept the ‘parent_id’ to represent the categories you made. Thanks for explaining

1 Like

Thanks for answering my questions!

1 Like