Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
choonie
Starting Member
3 Posts |
 Posted - 2004-09-03 : 19:51:59
|
| Hi, long time visitor, first time poster. I've been asked to design a Product Reviewing system, and it's going to my first time building something of a larger scale with potentially millions of records. Frankly, I'm a bit of a novice. I was wondering if someone could point me in the right direction in regards to its design?Say I have a product, and for each product, I can assign any number of reviewers (say upwards of a million users) to it. Associated with each User to Product relation are flags and dates which keep track of various states (if the user has been notified of the product, reminded, and completed reviewing it). Therefore, I have a UserProducts table that sits between, keeping track of these states. If I have many users and many products, this table will get large fast. Alternatively, I have thought about storing a comma delimited list of assigned Products in the User table for each user. This would be ok if I don't need to do lookups by project. Are there any other alternatives?Thanks for any input!-mike |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-03 : 20:20:02
|
well sine user - products are one to many that are 2 tables. different flags (one column using different values) and dates for a user should be kept in table Users.Flags (FlagId, FlagDescription, ...) should be third table.I don't see the need for UserProducts table... unless you have many to many relationship.Think about auditing (keeping history) on those 2 tables. so u need 2 more tables:UsersAudit and ProductsAudit. that way you can have a history of changesfor info on that see:http://www.nigelrivett.net/ under triggers.CSV's in columns are a bad idea to store data. a big NO NO.the more info you give us the more info we give you  Go with the flow & have fun! Else fight the flow :) |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-03 : 20:43:44
|
| To me it sounds like[User] -< [UserProduct] >- [Product] with flags in UserProduct table.Or[User][Product][Event]and a [UserProductEvent] related to the above 3 tables[event] contains of course "notified", "reminded" etc...rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
choonie
Starting Member
3 Posts |
Posted - 2004-09-03 : 20:49:38
|
| to spirit1:Thanks for the response! Much appreciated.If I read correctly, you say I should add "flag" to my Users table. However, I need a flag for each product the user is assigned to. I do believe I have a many to many relationship here, since a user can be assigned to any number of different products, and a product can be assigned to any number of users. Sorry if I did a poor job of explaining. Does this change your explanation?Thanks,mike |
|
|
|
choonie
Starting Member
3 Posts |
Posted - 2004-09-03 : 21:04:09
|
quote:
Originally posted by rockmoose
To me it sounds like[User] -< [UserProduct] >- [Product] with flags in UserProduct table.
This is how I envisioned it, with the dates as flags (notifyDate,remindDate,etc) and a NULL date signfying that the event hasn't occured. I know this isnt a generic solution, but I'm concerned about tablespace and query speed. I need keep track of dates for each event for statisical purposes.With [Events], I'm assuming I would have to do an insert into [UserProductEvents] every time a user was notified, reminded, and completed (a maximum of 3 records per user-product review). If I have 100,000 users and 100 products, and I want everyone to review every product, this means I could potentially have 3*(100000*100) = 30 million rows in the table. I guess this isnt extraordinarily large.. should I even be concerned?Thanks for the reply,mike |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-03 : 21:11:01
|
| well since it is many to many (i was hoping it wasn't , but it's no problem)i'd go with what rockmoose suggested. i'm not sure about the need for UserProductEvent table, because as he said flags should be in UserProducts (in my opinion too) which is the same thing as event. event is a flag and it should be in separate table. User (UserId, Name, LastName, ...)Products(ProductId, Name, Price, ...)UserProducts (UserId, ProductId, Flag(event), DateChanged).Flags(FlagId, FlagDescription, ...).auditing is also a big part of keeping track with chages, as i said before...Go with the flow & have fun! Else fight the flow :) |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-04 : 04:24:22
|
| The database design should support the business requirements.Generality will tend to "complicate" the design, sometimes this is a necessary requirement and sometimes not.In your case, if you indeed only have to store 3 dates for the userproduct relation then:UserProduct( UserID, ProductID, notifyDate, remindDate, reviewDate ) would be ok, null in date would indeed mean "no info available".On the other hand if you need to store additional info:e.g. UserProduct( UserID, ProductID, notifyDate, notifyBy, remindDate1, remindBy1, remindDate2, remindBy2,,, reviewDate, reviewScore1, reviewScore2,,, ) etc etc.. then further normalization is recommendedHow much flexibility does your business requiremnts require ?rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
|
|
|
|
|
database design efficiency