Db Theory Advice Multiple Catagories Per Item
Jun 14, 2004
My co worker designed a database where retail items can be placed in multiple catagories. This seems odd to me..... In general, Isnt it more normal than not to be only one catarory for each item? For example, lets say I was selling a bowling ball with a picture of Mickey Mouse on it. I can then find this item in the "Mickey Mouse" catagory or in the "bowling ball" catagory but in the database the bowling ball has only one catagoryID. When I worked for a multi-million dollar corporate retail store , an item was listed once in only one catagroy. But i am sure items can be viewed
I know there isnt a single rule, I am just looking for a solution. How should the database sturucture be built with this in mind starting out with what is listed below???? Mabey an attributes table?
Items
ItemID ItemName CatagoryID
Catagories
CatagoryID CatagoryName
View 6 Replies
ADVERTISEMENT
Mar 14, 2008
I am trying to figure out the best way to build a table that will hold information about our inventory of parts. We have several issues that I have been struggeling with trying to make this work. First capturing the basic part information is a no brainer, where I am having some questions is with how to assign categories, subcategories for each of these parts. The challenge comes because each part can belong to multiple categories and subcategories. For example we have 5 main items that would have categories, subcategories, parts. For example:
Item: Trailer
Model: model1, model2, model3
Category: Chassis
Subcategory: Upper frame, lower frame, electrical
Part: could be any thing that falls under one of the subcategories
Item: Trailer
Model: model1, model2, model3
Category: Gen set
Subcategory: engine, power, electrical
Part: could be any thing that falls under one of the subcategories
repeat...
My main items are trailer, shelter, power, heat, lights. Under the trailer and shelter items parts can belong all models or just a couple or just one model. My thought was to create a table for each item, ie: TrailerModels, ShelterModels which would have 100 plus columns, 1 column for each model, using a bit datatype to show which parts could belong to which model. This however seems to be quite a bit of work, because in order to use the models to thin down lists when searching for an item it would have query this table as well as the category and subcategory tables.
Anyone have some feedback about how I should approach this? I don't know if I was able to communicate the specifics of this very well but I can clear up details if anyone wants to throw some feedback my way.
Thanks!
yAy
View 3 Replies
View Related
Sep 28, 2000
Hi, can someone plz give me an idea of how to proceed with this...
I've got a server side cursor that retrieves a list of customers from a sql server table which match a specific criteria (those who have had orders in the yr 2000)
For each customer, I need to retrieve a list of 5 top items purchased along with dollars spent on each item by that customer.
I've tried to do this in a loop that uses a counter but probably my syntax was off and I'm not sure that this is better than a correlated join? Can someone show me a temlate of the appropriate syntax to use for this operation
I appreciate suggestions, thanks again.
Irene
View 2 Replies
View Related
Apr 11, 2007
hi I was just writing for some general advice. If I am to do many updates on a table is it okay to keep updating with many insert statements inside of a loop closing the connection each time? Here is some pseudo code to give you an idea:
cnn.open();
cmd = "insert into Cats values (red,4994,homer)";
for(int i=0; i<10000; i++)
{
cmd.executenonquery();
cnn.close();
}
View 2 Replies
View Related
Dec 6, 2013
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
View 5 Replies
View Related
Feb 29, 2008
Hello,I'm looking into offering a custom data driven web app that I wrote for an organization that I'm apart of to other similar organizations. I would be hosting the data and web application code on my dedicated server. This application is using the membership api supplied in .NET 2.0 and also has my own custom data tables within it.My question is what would be the best way to add clients to this? Should I simply create a new database for each new client like so: ACME_Database, ABC_Database, AAA_Database etc. Or should I add some sort of client "Tag" (tag meaning column within each datatable) to these databases and then update my SQL queries to process them accordingly. I imagine I could do both but I guess I need some advice from people that already had experiance with providing this kind of service. Thanks!Jason
View 4 Replies
View Related
Mar 6, 2007
Hi,
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
THANKS
View 3 Replies
View Related
Jan 31, 2007
Hi,
I am using ATL COM library application. It is using sql data base for fetching the records. Some times, i get the following error. could you please let me know, why this happens? This is not reproduceble every time.
(Error! hr=80040e21, hrDesc=Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work
Here is the code to connect to database which i am using.
CDataSource db;
CDBPropSet dbinit(DBPROPSET_DBINIT);
dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
dbinit.AddProperty(DBPROP_INIT_CATALOG, (const char *)bDatabase);
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, (const char *)bServer);
dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
dbinit.AddProperty(DBPROP_INIT_TIMEOUT, (short)150);
hr = db.Open(_T("SQLOLEDB.1"), &dbinit);
if (FatalError(hr, "db.Open", buf))
{
*iErrorCode = hr;
return S_OK;
}
Any help, appreciated.
Thanks,
Satish
View 1 Replies
View Related
Jul 12, 2007
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
View 1 Replies
View Related
Aug 17, 2007
How do I select * remaining Records of a table that don't fall true to the 2nd Select statement?
Select * From zz_2007_Booth_Visitors
NOT IN
Select * From zz_2007_Booth_Visitors
Where [Product Interest - Actuators] Is Null
And [Product Interest - Other Actuator] Is Null
And [Product Interest - Chillers] Is Null
And [Product Interest - Other Chiller] Is Null
And [Product Interest - Electronic Products] Is Null
And [Product Interest - Other network interfaces] Is Null
And [Product Interest - Fittings] Is Null
And [Product Interest - High Vacuum] Is Null
And [Product Interest - Other high vacuum actuators] Is Null
And [Product Interest - Pick& Place and Transfer] Is Null
And [Product Interest - Teflon Products] Is Null
And [Product Interest - Training] Is Null
And [Product Interest - Valves& Manifolds] Is Null
View 4 Replies
View Related
Oct 31, 2001
Hello,
I am having a problem with my team mates believing that primary keys need to be unique. I have the following data listed below and I am getting a violation of a primary key constraint. The primary keys for this table are request, business_req_id and test_case_id. I beleive the error is happening due to the data not being unique. Am I correct?
Thank you.
Anita
Request BUSINESS_REQ_ID BUS_TEST TEST_CASE_ID
B0K0037A1 1.1 1 7/31/01 0:00
B1C0015A4 4.1 1 8/8/01 16:53
B1C0015A2 2.1 1 8/8/01 16:53
B1C0015A1 1.1 1 8/8/01 16:53
B1C0015A3 3.1 1 8/8/01 16:53
View 1 Replies
View Related
Oct 31, 2001
Hello,
I am having a problem with my team mates believing that primary keys need to be unique. I have the following data listed below and I am getting a violation of a primary key constraint. The primary keys for this table are request, business_req_id and test_case_id. I beleive the error is happening due to the data not being unique. Am I correct?
Thank you.
Anita
Request BUSINESS_REQ_ID BUS_TEST TEST_CASE_ID
B0K0037A1 1.1 1 7/31/01 0:00
B1C0015A4 4.1 1 8/8/01 16:53
B1C0015A2 2.1 1 8/8/01 16:53
B1C0015A1 1.1 1 8/8/01 16:53
B1C0015A3 3.1 1 8/8/01 16:53
View 4 Replies
View Related
Nov 20, 2006
I have always heard that much of t-sql is based on "set theory". I had Set theory in high school and I remember it as being simple Unions, Intersections, Differences of Sets. By a Set I mean a collection such as {2,5,7,8,9, ...) That could well described a single row in a table. By unioning several of these rows we could end up with a table.
But how does that relate to t-sql such as
select * from <table name> Where <condition 1> ?
Is it simply that the result returned by the query is a Set? (if so, a Set is simply being used as a synonym for a Collection. No set theory involved.)
TIA,
barkingdog
View 1 Replies
View Related
Jun 28, 2006
I have been tasked with creating a Data Warehouse.
Problem is that old storage vs reporting debate.
I have determined that the data that I will recieve and store will be like follows (simplified form) for expandability
KEY FldKEy FldData DateTime AuditTrail
Daily I will use this data based on use input process this data into the following format and say
if fldkey/ flddata open a cycle.
populate row with null close date
if fldkey/ flddata closes cycle
update row with date
If fldkey/ flddata changes a cutable value
update row
if fldkey/ flddata changes a cutable value (type 2 table)
insert a row into detail update value and obsolete previous row.
KEY DateStart DateEnd FLDDATA1 FLDDATE2 Op_Cl_IND HEADER Record
KEY EFFdate OBSDATE FLDdata3 FLDData4 Detail Records
KEY EFFdate OBSDATE FLDdata3 FLDData4
KEY EFFdate OBSDATE FLDdata3 FLDData4
Problem: FLDKey is a finite count however the max is undefined.
IS there any way to solve the problem of not being able to nail down users to tell you what they want to cut by. What I have been instructed by mgr (old IDMS) is that they wish to see all on the FldData and have the ability to cut by all of it. However the Flddata could be anything (cannot be indexed).
400,000,000 rows at least.
Do I need to nail the users down or am I am missing something.
Sorry if so cryptic
:(
View 14 Replies
View Related
Jul 20, 2005
I understand that there is much involved in figuring up I/Othroughput, but I'm hoping the answer to the following question willbe a simple A, B OR C.Given the configuration below, what hardware component wouldbottleneck first.A. Drive Spindle ThroughputB. SCSI ControllerC. Other Component(if so what component?)Configuration:Controller: Ultra320RAID Config: RAID 5Drives on RAID: 14 - 73gb/15k - SCSI DrivesType of Writes: RandomQuestion Background:I currently have the configuration above on a local box. Our companyhas ordered a SAN and and has space set aside. What I'm trying toacertain is whether or not I'd be better off staying local or ifputting my DB on a SAN would be more beneficial.Other Notes:I've determined using a formula that determines the max theoreticalI/O operations/sec for the # of drives (spindles) and RAID Config...but I've not been able to establish a relationship betweenOperation/sec -> MB/Sec. I'm sure this is for obvious reasons...If anyone has had a related scenario, I'd be interested in hearingyour thoughts.Thanks,Ornac
View 9 Replies
View Related
Oct 25, 2004
Hi, our application is failing sometimes, with some select queries. After making traces in the database, I found the following error: Missing join predicate.
I googled that, and I only found this useless tip:
Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. The fix to this is to add a join predicate.
So, I dont know what a join predicate is... maybe I used it, but I don't know it by that name.
Thanks!
View 9 Replies
View Related
Jun 17, 2006
I'm struggling to find anywhere some recommended techniques for synchronizing two databases.
I am writing a smart-client app that will have its own database, and then connect and synchronize with the server when available.
Can somebody send me some links that discusses some of these ideas in detail?
Thanks
View 1 Replies
View Related
Mar 6, 2007
Hi all,
I have a general theory question for best practices about upsizing an msaccess 2003 split database design to use SQL server instead of the .mdb for data storage.
My data has grown close to msaccess limit, and ive started experiencing lost connections and corruptions frequently. So the next step is to upsize to SQL.
So far DTS seems to do a better job with bringing in the tables and data (then the upsizewizard).
Does any one have a suggestion on how to deal with the front end connecting the sql backend ?
i understand an ADP project file uses OLE connection, is that a better solution then linked tables directly into SQL threw ODBC ?
Reason i ask is the linked tables seem to not break as many things inside the code.
thanks for any suggestions,
jon
View 4 Replies
View Related
Jan 18, 2007
hi everyone
what the matrix's class name ?
where is the matrix's dll?
Can i create a new class extends the matrix?
I want to override the matrix's onpaint method.
Can i do this ?
why the table not have the column group?
View 9 Replies
View Related
Nov 4, 2015
I like writing concise and compact sql code without cursors if possible. My current dilemma has me stuck though.I have 3 tables, but one of them is optionally used and contains a key element of TimeOut to determine which Anesthesia CrnaID to use. It is the optionally used part that has me stumped.
Surgery table
CaseID
Patient
(Sample data: 101,SallyDoe 102,JohnDoe)
Anesthesia table
CaseID
CrnaID
(Sample data:
101,Melvin
102,Bart
102,Jack)
AnesthesiaTime table (this table is optionally used, only if the crna's take a break on long cases)
CaseID
CrnaID
TimeIn
TimeOut
(Sample data:
102,Jack,0800,1030
102,Bart,1030,1130
102,Jack,1130,1215)
Select Patient INNER JOIN Anesthesia produced too many case results. So, I figured out there is an AnesthesiaTime table that only gets used if the anesthesia guys take time-outs. That doesn't happen all the time. I could use TOP 1 on the Anesthesia table, but technically I need to read the AnesthesiaTime table and locate the last time and pull that crna, Jack. I'm not sure how to deal with an optional table. I believe the IF Exists will be pertinent, but not sure of how to build this query. I've tried subquery without success.
View 2 Replies
View Related
Oct 16, 2014
I am having trouble finishing the last bit of a report. The report shows orders that customers have placed that contain 0 promo items, All promo items (all items in order are promo items), and a mix of promo and non promo (at least 1 promo item and 1 non-promo item). Ive simplified this a bit for ease of understanding but lets assume we have 2 tables: A Promo table that contains the items on promotion and the dates that promotion is valid, and a Sales table, that contains the order number, order date, and sku ordered.
I've already written code that finds orders that have at least 1 promo item in them, and using that, I can determine what orders have 0 promo items in them. Where I am stuck is taking the orders that have at least 1 promo item in them, and separating them into orders that have only promo items, and those that have both promo and not promo items in them. Also, there are several promos throughout the year (called "Offers") so in my code below, you can see 2 different Offers ("JF" and "MA") with their corresponding dates they are valid. They will never overlap. My results also have to be split out by Offer so management can look at the results of each offer separately. Here is some code:
Code:
create table #Promos (
Offer varchar(2) null,
SKU int null,
StartDt date null,
EndDt date null
[Code] ....
So my results should show OrderNo A1111 in the Promo and No Promo group because of SKU 5 not being promotional during the time that order was placed. OrderNo A2222 should be in the Promo Only group because both SKUs on the order were promotional at the time the order was placed.
View 6 Replies
View Related
Jan 12, 2005
I want read
dr[2] = SqlDataReader dr1.item("lastmane");
from database
but I have error expected
I don't while
SqlDataReader dr1 = cmd.ExecuteReader();
dr1.Read();
dr[2] = SqlDataReader dr1.item("lastmane");
View 2 Replies
View Related
Apr 23, 2008
select i.a,
w.c,
w.e
from table1 i,table2 w where
i.a=w.a (Here c is the primary key for each a)-iam getting o/p like this
a c e
aaa x 2
aaa y 10
bbb y 5
ccc x 3
ccc y 4
i need o/p in this way (ie sum of e for each a)
a e
aaa 12
bbb 5
ccc 7
Is it possible
Thanx
View 1 Replies
View Related
Jan 11, 2014
I´m trying to get a query where I´ll get an answer like this:
+--------------+----------------+----------------+------------------+-----------------+
| champion_id | champion_name | champion_item1 | champion_item... | champion_item16 |
+--------------+----------------+----------------+------------------+-----------------+
| 1 | Legolas | boots | ... | sword |
+--------------+----------------+----------------+------------------+-----------------+
This is the scenario how I set up my tables (let me know if I should change it):
table champions
+--------------+----------------+
|champion_id | champion_name |
+--------------+----------------+
| 1 | Legolas |
+--------------+----------------+
table equipment
+--------------+----------------+------------------+-----------------+
| champion_id | champion_item1 | champion_item... | champion_item16 |
+--------------+----------------+------------------+-----------------+
| 1 | 5 | ... | 22 |
+--------------+----------------+------------------+-----------------+
table items
+--------------+----------------+
| item_id | item_name |
+--------------+----------------+
| 5 | boots |
+--------------+----------------+
| 22 | sword |
+--------------+----------------+
But I still don´t know how to call the equipment table and get champion name and item names for the numbers. What did I do wrong and how can I fix it?
The foreign keys are in place.
View 5 Replies
View Related
Dec 14, 2004
Not 100% sure how to do this so I would appreciate some directions. I have 2 tables, Systems and Contacts. A system can have 1 to infinite contacts and contact can have 1 to infinite systems. So I use a 3rd table Sys_Con to add the contact needed for each system.
Now my question is once I add the System and contact how do I know for sure which ContactID to add in my Sys_Con table? Because while I am doing this operation maybe someone can add an other contact. So is there a way for my store proc Add_Contact to return the contactID needed for my store proc Add_Sys_Con ?
TABLE Sys_Con
SystemID int
ContactID int
Table Contact
ContactID int Identity
ContactName
...
Table System
SystemID
...
View 3 Replies
View Related
Nov 25, 2007
Hi,
I'm a bit stuck here.
I have a table called STM_COMM and it contains following fields: id, member_id, email_addr, tel_nr, fax_nr
A member can have multiple email addresses, telephone numbers and faxnumbers. so we could have something like this:
id member_id email_addr tel_nr fax_nr
-------------------------------------------------------
1 5 info@test.com
2 5 555687 568774
3 66 test@go.nl 65897854
4 5 another@one.com
What I would like to have is only ONE (1) row PER member with only the FIRST (not max!) entered known data. In the above case, I'd like to have this as result:
member_id email_addr tel_nr fax_nr
----------------------------------------------------
5 info@test.com 555687 568774
66 test@go.nl 65897854
I've tried lots of things (max, inner select ...) but I can't seem to figure it out.
Thank you for the help !
Christophe
PS: I'm working on a sql server 2005
View 1 Replies
View Related
Mar 7, 2007
How can I get the value of the last Item in a coums?SELECT COLUMN FROM DATABASE ORDER BY COLUMN DESC?something with Fields(0).value?Thank you in advance
View 4 Replies
View Related
Feb 27, 2007
Hi gurus
I have sql server 2000 that was working fine until today(figures), today I opened EM and under sql server group where used to be all my information now there is NO ITEM, nothing shows(database,Nothing) The server is working as I had no complaints from my user on data not been saved or getting data from but its strange that I can not see all my info like I used to.- If I go into sql query analizer, it ask me to connect to local which I do and I can see all my databases from there but once I close that window ,on my EM nothing shows.
I checked all services andevery one of them seems to be up and runnig
Thanks forall the help I can get
View 4 Replies
View Related
Aug 8, 2007
Hi Every Body,
I have one table it's called -Access.It contains two columns name of that is Door,Emp_Id.
example Door Emp_Id
10 1
10 2
10 3
11 4
12 5
11 1
this is the value in that table.......
I want get Which Door number maximum Access from the table.....
Thanks & Regards,
S.Sajan
View 7 Replies
View Related
May 2, 2007
Hi Everyone I have an ADP and I need to create a report that will give me the total number of each item. I need the report to show how many [Violation Type], and the total [Loss] per violation. Can anyone help please
Code Snippet
CREATE TABLE [dbo].[Revised_MainTable] (
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Inspector] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation Type] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss] [money] NULL ,
[Loss Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Security/GC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
View 9 Replies
View Related
Feb 28, 2008
Hi all,
I have some problem about parameter to query data. My parameter is available from dataset but it doen not have blank or null value. So when I want to select this...
SELECT *
FROM mytable
WHERE (@param_person_id IS NULL OR @param_person_id = person_id)
cannot success. This statement means "if @param_person_id has null value, this statement is select every person_id. On the other way, if @param_person_id is not null value, this statement select only specific person_id".
So I want to add blank or null value to my dataset for working with select statement.
How can I do?
Thank you very much
View 1 Replies
View Related
Feb 19, 2007
Is there anyway to sum a value that resides in a textbox?
ie; =Sum(ReportItems!MyTextBox.value) ?
View 7 Replies
View Related
May 19, 2007
I added something by mistake! If I delete it it gives me an `are you sure?` message that makes me think it'll actually delete the file from the hard drive. As I've accidentally added something from SQL Server (I was trying to add a database and got carried away) I don't think that's such a good idea. I've googled and found this:http://msdn2.microsoft.com/en-us/library/1ee8zw5t(VS.80).aspx which helpfully states:----- To remove a solution item In Solution Explorer, select the item you want to remove. On the Edit menu, choose Remove. ----- which would be great, if only the Edit menu had a remove option. Any ideas?
View 2 Replies
View Related
