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 |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2005-10-25 : 11:51:36
|
| Hi there,I have several tables holding things like restaurants, clubs, days out and for each of these entities an admin needs to be able to choose a top 5.I figure that in order to preserve 1st normal form, I need one top 5 table for each of the entities even though all of the top 5 tables contain just a reference to an entity and a value indicating its order. Like this...TopClubs========ClubID int PK, FKOrder intSo even though all these top 5 tables I'm planning are all almost the same, do I need to have one top table for each entity or is there a way of collapsing them into one table without breaking 1st normal form?Cheers, XF. |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-25 : 12:48:39
|
The top 5 tables could be made as views, but I guess you are doing this as a f-a-s-t access path to top 5.If you collapse them, just include the "type" as part of the key (club, restaurant etc..), but I don't see the benefit,you need to add conditional WHERE processing instead of declarative "choose the right table/view" processing.What if you need top 5 on one page, and top 20 in another place ?You could rank them in the entity tables themselves, since you are going through this ranking process anyway.Create a proc : getTopEntities(@number, @entity)...exec getTopEntities(5,'clubs'), Edit.Go with what seems most logical in your system.rockmoose |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-10-25 : 13:23:08
|
Thanks for your reply.quote:
If you collapse them, just include the "type" as part of the key (club, restaurant etc..), but I don't see the benefit,
But if I collapse them doesn't that mean I either have one column for each type of entity in order to be able to add relationships? Like so..OrderTable==========Clubs int FKDaysOut int FK...Order intWhich means that each row will have lots of null values. Or I have one column for all entities but no referential integrity...OrderTable2==========Entity int PKEntityType int PKOrder intquote:
The top 5 tables could be made as views, but I guess you are doing this as a f-a-s-t access path to top 5.
Do you mean that the actual ordering values would have to be in the underlying entity tables?Cheer, XF. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-25 : 17:43:02
|
| I was thinking OrderTable2 in your example.However storing different "domains" in the same column is not relational at all.(It could somehow be justified if the different "types" of entities are "subtypes" of a broader domain (location/venue/passtime/thingtodo???))The example (OrderTable) is not normalized since the columns are not mutually exclusive.>> Do you mean that the actual ordering values would have to be in the underlying entity tables?I think that is a possible solution, "select ... from entity where rank < 6", work done.The important thing is to put the rank where it logically belongs.a) it's dynamically computed when retrieved (a view ?)b) it's a property of the entity itself (in the entity table)c) it's a computed value that we use for convenience (maybe separate table as you suggested, maybe in the entity table)d) it's just a computed value that we can store anywhere and we don't care about data integrity, normalization etc... we just want it somewhere accessible.rockmoose |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-10-25 : 20:59:23
|
quote:
(It could somehow be justified if the different "types" of entities are "subtypes" of a broader domain (location/venue/passtime/thingtodo???))
Funnily enough, 3 out of the 5 entities being ordered share a 'CompanyAddress' row. So I could do OrderTable2 for those 3 and be relational. That would save 2 tables but then it would also add an extra case to my UI Mapping code which is bad.The ordering isn't computed at all. Its chosen by the admin. And it is just the top 5 of each and having to order all the others at the same time is just not relevant or particularly pratical given the number of items. So I don't see the ordering can be a property of an entity - the top 5 list is a seperate piece of information which an entity may have nothing to do with. So I'm happy with extra tables. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-26 : 03:39:03
|
| >> Its chosen by the adminDoes that mean he never gets bounced from the clubs, and get xtra big drinks ? |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-10-26 : 09:07:54
|
| I think he gets free lap dances at the local strip club. |
|
|
|
|
|
|
|
|
Design Clarification