CODEWITHSUNDEEP
database-design
Alexander
Alexander

Reputation: 4219

Relationship between User and Purchases in a Database

I'm relatively new to database design, and I'm having a bit of trouble with a certain concept -

From what I've learned, relational databases should always have information separated, and never have duplicate information.

I am creating a simple website where a user can purchase a product. The two tables might looks something like this:

User:

enter image description here

Product:

enter image description here

How could I set up a relationship between these two tables so that multiple users can own the same product without using an array? The only solution I have thought is to create a third table called "UserOwnership" or something like that, with a foreign key for each user_id that corresponds to a foreign key for each product_id. However, I didn't think that was a good idea, because all users will have unorganized entries in a massive database, like the following.

enter image description here

So for instance, user1 owns both product1 and product2. However, all other subsequent users are stored in the same table, and I presume this would get messy and unusable with enough users.

My other solution was to use a list of information for each user that corresponds to a product id, for instance:

enter image description here

user1 owns product1 product2 and product3. This seems like it would work great, but it violates the rule of storing multiple values in one cell.

How can I solve this problem?

Upvotes: 0

Views: 1209

Answers (3)

Jacob Errington
Jacob Errington

Reputation: 135

Using an association like you did is an excellent solution, and performance should be fine.

Relational database management systems don't have to scan the whole table each time if you help them out. Since the primary key of the association table would be the pair (user_id, product_id), which isn't really useful, you can create indexes on each of the associated columns user_id and product_id. Think of it this way: it's unlikely that a single user will account for over 5% of all the rows in your association table, and the index will allow the RDBMS to quickly narrow down the search to just the relevant rows in something like O(log n) time. If you have a billion users, it'll take the database only some 30 steps to find the rows for the products purchased by a given user.

Indexes do add overhead, so don't put them everywhere! The Postgres documentation has an excellent discussion of indexes if you want to know more about the pros and cons.

Upvotes: 1

Ian Gilbert
Ian Gilbert

Reputation: 21

In a situation where you are dealing with purchases, you more than likely would want to keep track of the details of the transaction such as time, user account, products, amount paid, etc. Because purchases are separate from the users or products themselves, they can have their own table with a primary key of an order number, or a composite key made up of user ID and product ID (assuming each user can only buy the same product once)

https://i.sstatic.net/3qkPH.png

This way you can use the purchases table to look up what products are owned by a user using the UserID, or which users own a certain product.

Upvotes: 2

johnbwu
johnbwu

Reputation: 31

Your first suggestion is what many people decide to do. A UserProductAssociationtable with a row for each User-Product association, with FK's to the User and Product table.

Upvotes: 2

Related Questions