Database design help

mishy

I am designing a database to be used for a classified adverts website. I have 2 tables at the moment but I have been told I would need atleast 3 tables but I am not quite sure what to put into the 3rd table. The recommendations have been to add a 3rd table that will link the 2 tables together. It is for a project at school so I am not able to use my trustworthy wordpress or other open source CMS. I have attached a screenshot of my proposed Database and tables and will appreciate any recommendations from others.

ratbuddy

What about an orders table that joins customers with what products they bought?

edit: Nevermind, I didn't read closely enough, I saw the picture and assumed you were doing an ecommerce thing.

NotHackingYou

Likely, you need a third table known as a junction table, assuming many users can have many products. Each row would hold the primary key of a user and the primary key of a product you wish to associate with the user. This would allow you to tell which users are associated with which products.

Edit: Looking close at your diagram you have the userID in the products table. This would mean to me that each product can only be associated with one user. If this is correct then your don't need another table.

Can you clarify the intended relationship?

santaowns

You could add a separate table for users post that contains the post, pictures title, and one that is the product/ type of products user can post.

knownhero

You can have a table of the regions in the UK eg. London, Manchester and link that to both the USER TABLE for a location and the product table on Location. So in theory you can then do a search on "What items are in my area".

knownhero

You could do something like the image I attached and create a view over it to query it for locations:

SELECT        dbo.Products.Title, dbo.Products.RegionID, dbo.Users.Username, dbo.Users.FirstName, dbo.Users.EmailAddress, dbo.Regions.RegionNameFROM            dbo.Products INNER JOIN
                         dbo.Regions ON dbo.Products.RegionID = dbo.Regions.ID INNER JOIN
                         dbo.Users ON dbo.Products.UserID = dbo.Users.ID AND dbo.Regions.ID = dbo.Users.RegionID
WHERE        (dbo.Products.RegionID = 6)

mishy

CarlSaiyed wrote: »

Edit: Looking close at your diagram you have the userID in the products table. This would mean to me that each product can only be associated with one user. If this is correct then your don't need another table.

Can you clarify the intended relationship?

I did not have any ideas on how the relationship of the third table would be since I thought I had met all the desired objectives by adding the user ID in the products page. Yes, a product can only have one user associated with it.

Thanks to everyone for the responses, I think I might go ahead with the idea of adding a location and then creating a third table that joins the user and the product together based on location.

knownhero

mishy wrote: »

I did not have any ideas on how the relationship of the third table would be since I thought I had met all the desired objectives by adding the user ID in the products page. Yes, a product can only have one user associated with it.

Thanks to everyone for the responses, I think I might go ahead with the idea of adding a location and then creating a third table that joins the user and the product together based on location.

That's what I would do to start off with. Then you can create views etc to see what's in my area.

mishy

knownhero wrote: »

That's what I would do to start off with. Then you can create views etc to see what's in my area.

Thanks for the advice

RobertKaucher

I think we are kind of getting ahead of ourselves as you have not actually explained your requirements.

Is the correct relationship in your model that each USER should have 0 or more products and that each PRODUCT must have only 1 user?

Also, if you use REGIONS as a junction in the way depicted there will be a couple of issues:

1. A user and his products could have different regions. (Is that what you expect?)
2. A user can only be associated with 1 region (what if I want to advertise for products in the city where I live and where I work?)

And to add another layer of complexity here, should different users be able to sell the same product? For example on Amazon, if I am selling a book with ISBN 978-0956361431 other users can sell that same book. This allows me to compare the prices for an individual item advertised by multiple users.

A better representation of the model might be to have a bridge table between the USER and the PRODCUTS called USER_PRODUCTS (or something similar) which requires only the following:



ID [int] (Surrogate Primary Key)
UserID [int] (FK to user table)
ProductID [int] (FK to Product Table)



This allows multiple users to not only sell the same products but also allows a single user to sell multiples of the same product. Meaning if I have two copies of the TIME magazine featuring the lunar landing but one is in good condition and another is in excellent condition I can sell them both at different prices. Now if you want to add additional attributes like price, condition, etc to the USER_PRODUCTS table you can do that as well.

Now you need to ask yourself if a user_offer should be allowed to be posted in two or more regions? For example I live nearly exactly between Cincinnati and Dayton. I would likely want to advertise my products in both cities. So should REGION then have a bridge table to USER_PRODUCTS called USER_PRODUCT _REGIONS? It's all about modeling the reality your user will expect. If your user base will expect to be able to sell multiples of the same product with some different attributes (condition, for example) you had better not make them have to fill in all the details (name, ISBN, or whatever) again. Also, if your users are going to expect to be able to post their offers in multiple "regions" then not allowing them to do so will be frustrating. Not only that but deciding that you do want to allow this post hoc will be a hard, schema breaking, change to make.

mishy

RobertKaucher wrote: »

I think we are kind of getting ahead of ourselves as you have not actually explained your requirements.

Is the correct relationship in your model that each USER should have 0 or more products and that each PRODUCT must have only 1 user?

Also, if you use REGIONS as a junction in the way depicted there will be a couple of issues:

1. A user and his products could have different regions. (Is that what you expect?)
2. A user can only be associated with 1 region (what if I want to advertise for products in the city where I live and where I work?)

Thank you for your input and sorry for the late reply. You have raised some very valid points but for my website it is just going to be a basic website to demonstrate my Php and Mysql knowledge.

To answer your questions, yes a product will only have one user but a user can have multiple products. The project is going to be a university classified website so we will keep the location to preset locations and a user to only have one university location.

If I was doing this outside of a school project I would have gone for a framework rather than writing and securing the whole project myself.

RobertKaucher

I understand. My point was really just that we did not know the specification that you were given for your data model. In your original post, you showed your existing model and said that it was incomplete because you were told you'd need at least 3 tables but not what that third table will do. Some people came up with good guesses, but without knowing what the relationship was intended it's hard to say. I also wanted to stress that choosing any given model will always have advantages and disadvantages. But you are probably are aware of that.

mishy

RobertKaucher wrote: »

I understand. My point was really just that we did not know the specification that you were given for your data model. In your original post, you showed your existing model and said that it was incomplete because you were told you'd need at least 3 tables but not what that third table will do. Some people came up with good guesses, but without knowing what the relationship was intended it's hard to say. I also wanted to stress that choosing any given model will always have advantages and disadvantages. But you are probably are aware of that.

Thanks. I had wanted to expand the project but due to time limitations I am now keeping just to the requirements.

phoeneous

Somewhat on topic but I just discovered sqlfiddle and it has helped me when I'm not by ssms.

SQL Fiddle

RobertKaucher

It's very nice for sharing queries. We've used in in the SQL Server cert forum.