Advice on Database Design

[iceangel89]

I am thinking of doing a fake PC company site for my ASP project. so what they will have is a chat, products with reviews, and users can have "Buddies".

So my DB so far (Tables):

• ProductPC - ID, Processor, RAM, HDD, Graphics, LCD ...
  
• ProductHardware - ID, Title (Like "Intel C2D E6600"), Description, Price, Rating (0-5 stars, so integer), Category (CPU, HDD, Graphics)
  
• ProductSoftware - ID, Title (eg. "Adobe CS3"), Description, Price, Rating, Category (eg. Design, Programming)
  
• Reviews - ID, ProductID, Title, Content, Date
  
• Chat - ID, TopicID, Title, Content, Date
  
• Users - ID, Username, Password,
  

The problem, how do i connect Reviews to the products since they are from diff tables.

How do i get the "buddy" system working

Chat i think its not as simple as that

But i just need a simple ASP project, so no need to get too complex, but i still hope to learn as much.

[RedArrow7997]

Combine your three product tables together into one table, if you don't then you are going to need to create three reviews table. because if some one does a review for "intel c2d e6600" which as an id of 1 and "adobe cs3" which also has an idea of one then which of the two does the review go with.

i would make one large table with all of the information need for three product types. that way if will be easy on you when you are asked to do search for different products. microsoft doesn't only make software they also make some hardware.

with the chat subject, are you talking about a forum instead of chat. chat will be hard to for a class project.

the idea you have for user is about right, i would add a couple more fields like first name, last name, email and maybe some of the im services(aim, yahoo, msn).

[ripken204]

what type of database are you using?

[RedArrow7997]

what type of database are you using?

i am think it is either access or sql server seeing that it is asp project.

[iceangel89]

i am using SQL Server from Visual Studio 2005.

But if i combine all products, what happens is PCs have more fields that usually Hardware & Software, like they have a Processor, RAM while HW/SW don't have. then many fields will be NULL. will that be OK or Good?

As for the Chat it will be a simple 1. like user can post ...

I was thinking the ChatPost Table or Forums: (if its possible)

- TopicID

- PostID

- UserID

- TopicTitle

- Content

- Date

[ripken204]

well you can leave them in separate tables. i only have experience with mysql and im assuming it is almost identrical as mssql. all you would need to do is:

SELECT * FROM Reviews,ProductHardware WHERE Reviews.ID=ProductHardware.ID AND Review.ID="whatever_id_i_want"

this is assuming that the ID's all corrisbond with eachother. is that how your keeping track of things? or are the ID's specific to that table, such as a key?

what this will do tho, is take those 2 table and take the id u want to find, then take that single if from both tables. then you call call whatever data u want from those 2 tables. you could do a left join if u want, knd of compine the tables that way.

Edited June 5, 2007 by ripken204

[RedArrow7997]

i am using SQL Server from Visual Studio 2005.

But if i combine all products, what happens is PCs have more fields that usually Hardware & Software, like they have a Processor, RAM while HW/SW don't have. then many fields will be NULL. will that be OK or Good?

As for the Chat it will be a simple 1. like user can post ...

I was thinking the ChatPost Table or Forums: (if its possible)

- TopicID

- PostID

- UserID

- TopicTitle

- Content

- Date

maybe you should have a one many table with

id

description

type of product

then use it to link to other tables with more related to the type of product, software, hardware and computers. i assume that you will have sort of search in this app, with out a some sort of central table that search will be really hard to complete.

[iceangel89]

ripken204 that helps, and i am using the IDs as a key.

but i didn't get the part:

"this is assuming that the ID's all corrisbond with eachother"

RedArrow7997, i understood u as :

have a central table: for ProductType

- ID

- Description (eg. This table is for PCs)

- Type (eg. PC, Hardware ...)

am i right?

Then, connect them to PC, Software tables

but to me (as a learner), how do i do that (Search)? i was thinking few tables are easier? like if the page is for Adding PCs then in the VB i just need to add to the appropriate table? but if i do your method, i need to chk

If ProductType = ??? Then

Insert into This table.

No offense just want to clarify.

[ripken204]

by that quote i mean, do the ids match? are the ids product numbers?

like if you have an e6600 in ProductHardware, it may have an id of 1232 for example.

would it also have an id of 1232 in Reviews?

i'll need you to clarify first tho. what exactly do you want to do with all of this date? i was thinking that you just wanted to retrieve it?

[RedArrow7997]

RedArrow7997, i understood u as :

have a central table: for ProductType

- ID

- Description (eg. This table is for PCs)

- Type (eg. PC, Hardware ...)

am i right?

Then, connect them to PC, Software tables

but to me (as a learner), how do i do that (Search)? i was thinking few tables are easier? like if the page is for Adding PCs then in the VB i just need to add to the appropriate table? but if i do your method, i need to chk

If ProductType = ??? Then

Insert into This table.

No offense just want to clarify.

i think you are trying to create a bigger project then you can handle right now, you should start maybe with just a single product, lets say computers. get everything to work that you want. then add the other stuff. that way your are only dealing with one set of tables for your products.

[iceangel89]

ripken204, i didn't intend for the (Review)IDs to match, to search i would use the ProductID Foreign Key in Reviews table, so ProductID would match. reviewID would be just a Key just for nothing for now.

RedArrow7997, i think u are right, i seem to always expand of my projects LOL, really, 1/3 my class does that actually, top class u see. but the competition makes things tough. i guess if i am in a lousier class all my Bs would become As and As become Distinction. LOL anyways i think its good to learn more than u are taught.

[RedArrow7997]

i am not saying that it is a bad idea to do those three things, but start out with one then add another one when you have work out all of the bugs form the first one. if you try to add all three together then you will never finish your project.

[ripken204]

ya its really hard to start off with a s*** load of material and trying to make something of it.

anyways, you still havnt described your project that much.

it is extremely easy to connect your hardware table with the reviews table since you will have the hardware id can = the review product id

[iceangel89]

Is it ok to have a DB Structure like this where Hardware from PC comes from a Hardware table? i will have many Foreign Keys in the PC table. eg. ProcessorID - HWID, HDDID - HWID... etc. then to select the item, i can do something like

SELECT * FROM Hardware, Category 
WHERE 
   Hardware.CategoryID = Category.CategoryID AND 
   Category.Title LIKE 'Processor'

I hope the code is right i hand typed it