Some kind of akward "group" database structure.

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Some kind of akward "group" database structure.

Author

Topic

Treeko
Starting Member

3 Posts

Posted - 2011-03-01 : 14:47:09

Greetings one, greetings all! I hope someone here can help me.

Alright, I'm working on a database for an auction. So far, the "main" table is the one that holds data for the individual items that are being auctioned off, holding information such as Item Name, Item Description, Price, etc.

One of the requirements is that they want to be able to make arbitrary groups out of items in the database.

And this is where I'm stuck. I originally wanted to make a new "tblItemGroup" table, and initially make every item part of one item group, but I ran into problems with that.

Now I'm trying to make a "tblItemGroup" table and have a foreign key field "intItemGroupID" in the "tblItem" table such that if the "intItemGroupID" field in "tblItem" is NULL, then it is just a lone item, but if it is not null, then it must belong in a group. I'm running into problems with this as well which we may or may not get into.

At the very least, some help on what direction I should go to handle this kind of requirement would be great!

o.o

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-02 : 11:24:33

I think that first the groups must be established.

tblGroups
ID Primary Key Identity
txtName varchar(100)
...whatever other columns you need for a "group"

I would then think that a table like tblGroupItems would come into play

tblGroupItems
ID Primary Key Identity
intGroupID Foreign key pointing back to tblGroups
intItemID Foreign key pointing back to tblItem
...any other columns to track what items are part of what groups

My thought on this approach is that if an item is not in tblGroupItems then it clearly does not fall into any groups. The other advantage to this is that an item could be part of multiple groups if you wanted them to be.

Hey, it compiles.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-11 : 05:43:47

Maybe you want something like:

table itemGroup(itemGroupId, itemGroupName, GroupingCriteriaId)
table groupingCriteria(groupingCriteriaId, GroupingCriteriaName)
table itemGroupMember(itemId, itemGroupId, displayIndex)

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources