April 13, 2012 at 5:57 am
Hi,
I'm pretty much a noob, so please excuse the lack or terminology or understanding at the moment.
I have a stats table - there are quite a few columns in the table but the crux is that I collect stats at 3 different levels.
1) StoreId
2) StoreId/CategoryId
3) StoreId/CategoryId/SubcategoryId
Where information isn't collected the columns remain null. So for example StoreId/NULL/NULL would be stored.
The first question I have is should I put foreign key constraints on this kind of table? It simply collects information, I roll it up into usable information and then re-attach it to live Stores.
The second question is, if I should constrain it how do I do so effectively?
As columns 2 and 3 can be null (3 can be null or 2 & 3 can be null) foeign key constraints are skipped over when using a primary key containing all 3 columns, so I guess I need to have 3 separate constraints.
1) PK StoreId
2) PK StoreId/CategoryId
3) PK StoreId/CategoryId/SubcategoryId
The issue I have is that although tables 1 & 3 exist, 2 does not, so I will have to create a table purely for the purpose of constraining entries into my table. I don't know whether this is the right thing to do or not, so any advice would prove useful.
Thanks in advance.
April 13, 2012 at 6:55 am
First thing you do while working with tables is to NORMALIZE the data. Check out this link:
To Normalize the data in your case, you need to have three different tables: Category, SubCategory, Stores(which can all have primary keys as constraints).
Once you have taken care of data redundancy in your tables by NORMALIZINg them, you can move forward freely without worrying about any kind of discrepancies in the way you want to use the data.
Your situation is a very good example.....the tables were not Normalize....so you were not able to set constraints. The very next step in Designing the tables caused you this problem....just because the tables are not NORMALIZED. Think of the no. problems you'll be facing(as they will come) because the tables are not NORMALIZED.
I think you should definitely make three master tables for category, subcategory and Stores. This will help you in enforcing constraints and will also keep you from getting into such sticky situations in the future.
April 13, 2012 at 7:24 am
Thanks for the reply.
Splitting the table into 3 won't resolve the issue.
The problem is that I don't have a table keyed StoreId/CategoryId so I've nothing to constrain against.
April 13, 2012 at 1:20 pm
What does this data represent? Are Categories of items a store carries? Are Subcategories as it sounds, subcategories of Categories?
Vinu512 is on the right track, and there are ways that you can set up these tables better to resolve your issue, but a better understanding of the scenario would be helpful. For instance, if you have:
create table tblStore (StoreID int)
create table tblCategory(CategoryID int)
create table tblSubCategory(SubCategoryID int)
to your point, there's no reason you cant make an intermediate table called
create table tblStoreCategories
(
StoreID int,
CategoryID int
primary key clustered (StoreID, CategoryID)
constraint myFK foreign key (StoreID) references tblStore (StoreID)
constraint myOtherFK foreign key (CategoryID) references tblCategory (CategoryID)
)
April 13, 2012 at 10:16 pm
Gabe T. (4/13/2012)
What does this data represent? Are Categories of items a store carries? Are Subcategories as it sounds, subcategories of Categories?Vinu512 is on the right track, and there are ways that you can set up these tables better to resolve your issue, but a better understanding of the scenario would be helpful. For instance, if you have:
create table tblStore (StoreID int)
create table tblCategory(CategoryID int)
create table tblSubCategory(SubCategoryID int)
to your point, there's no reason you cant make an intermediate table called
create table tblStoreCategories
(
StoreID int,
CategoryID int
primary key clustered (StoreID, CategoryID)
constraint myFK foreign key (StoreID) references tblStore (StoreID)
constraint myOtherFK foreign key (CategoryID) references tblCategory (CategoryID)
)
Thanx Gabe....Exactly my point. Doing this is no big deal and would make life so much more easier for the OP and for any other people who may be working on the tables.
From whatever tables you have, you should export the distinct and NOT NULL values for StoreId, CategoryId and SubCategoryId and make individual Master Tables to house this data(Stores Table, Categories Table and SubCategories Table respectively).
From that if you want you can construct various tables/views depending on the various Business Requirements.
How much time does it take anyway??
April 14, 2012 at 3:13 am
Vinu512 is on the right track, and there are ways that you can set up these tables better to resolve your issue, but a better understanding of the scenario would be helpful. For instance, if you have:
A brief overview:
I have a simple menu structure where parent Categories have child Subcategories. An example of this might be Travel - Flights. There can be a many to many relationship so there is a bridge table in place betwen the two tables.
I.e. the Subcategory 'Travel Insurance' might appear under 'Travel' and 'Insurance' categories.
I have a table of Stores. Stores have 1 or more Subcategories in a tables Stores_Categories. Stores are related to Subcategories because these reflect the retailers sector, wheras Categories are simply logical groups of Subcategories to make my menu structure look pretty.
I.e. the Store 'Monsoon' might fall under Category 'Fashion & Clothing', but their actual retail sector is 'Womens Clothing'.
I'm trying to be clever when it comes to searching for retailers/products hence the heirarchies. I also want to maintain a popularity index for of the levels, which are:
1) I have a list of stores spanning multiple Categories, so I want to display the most popular Store.
2) I have a list of Stores that have been narrowed by Category, so I want to display the most popular store in the category (This can be different from 1).
3) I have a list of stores that have been narrowed by Category/Subcategory, so I want to displat the most popular Store in the Subcategory (again this can be different from 1 and 2).
Thats about it in a nutshell.
April 14, 2012 at 3:17 am
Thanks for your advice guys.
I am coding a website for my own business. At the moment I have to be a jack of all trades, and I'm certainly a master of none yet.
Unfortunately I don't have colleagues, to lean upon so this has been a great help.
April 14, 2012 at 3:35 am
webbies (4/14/2012)
Vinu512 is on the right track, and there are ways that you can set up these tables better to resolve your issue, but a better understanding of the scenario would be helpful. For instance, if you have:
A brief overview:
I have a simple menu structure where parent Categories have child Subcategories. An example of this might be Travel - Flights. There can be a many to many relationship so there is a bridge table in place betwen the two tables.
I.e. the Subcategory 'Travel Insurance' might appear under 'Travel' and 'Insurance' categories.
I have a table of Stores. Stores have 1 or more Subcategories in a tables Stores_Categories. Stores are related to Subcategories because these reflect the retailers sector, wheras Categories are simply logical groups of Subcategories to make my menu structure look pretty.
I.e. the Store 'Monsoon' might fall under Category 'Fashion & Clothing', but their actual retail sector is 'Womens Clothing'.
I'm trying to be clever when it comes to searching for retailers/products hence the heirarchies. I also want to maintain a popularity index for of the levels, which are:
1) I have a list of stores spanning multiple Categories, so I want to display the most popular Store.
2) I have a list of Stores that have been narrowed by Category, so I want to display the most popular store in the category (This can be different from 1).
3) I have a list of stores that have been narrowed by Category/Subcategory, so I want to displat the most popular Store in the Subcategory (again this can be different from 1 and 2).
Thats about it in a nutshell.
From you example, I can make out the following:
1. Category - Fashion & Clothing
2. SubCategory - Women's Clothing
3. Store - Monsoon
You say that,"Categories are simply logical groups of Subcategories to make my menu structure look pretty." But still you must be storing these Categories physically, right?
So, why not store the data like this:
1. tbl_Categories(First Table) : Contains pk_Categoryid(Primary Key)
2. tbl_SubCategories(Second Table) : Contains pk_SubCategoryId(Primary Key) and fk_Categoryid(Foreign Key referencing tbl_Categories.Categoryid)
3. tbl_Stores(Third Table) : Contains pk_Storeid(Primary Key) and fk_SubCategoryid(Foreign Key referencing tbl_SubCategories.SubCategoryid)
Since, you are creating a site. You should take care of the data at the database level so that no discrepancies creep up in your site.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply