|
There is probably a really easy answer to this series of questions but I'm sort of a newbie in designing a database.
I know what a relational database is. I'm just not that familiar with designing one. My problem: Let's say I want to design a GUI interface for a kiosk for a library or bookstore. There could be several implementations and it could even be thought of as HTML links but the GUI looks nicer. The reason for this comparison is because I want the categories and sub categories to be dynamic, based on information stored in tables and obtained through queries.
I was thinking that I could have a table of main categories called "cats", and a table of sub categories called "sub cats", and of course a table for all of the books with a sub-category field. If a GUI is used, a blob for the image that represents the category or sub category can be assigned a field in the tables.
If I query the cats table and display all of the menu selections for the main categories and then when a main menu selection is made, say for "biographies", I can query for the second level menu items by looking for records in the "sub cat" table with a "Parent" field of "Biographies" which I imagine this could be only two records like "Biographies" and "Auto Biographies" and then drill down to the books table with a query to either of those two sub categories if a menu selection is made.
The problem I have understanding is a many-to-one relationship and how to implement it. I've discussed the first and 2nd level menus but if there is a third level menu and a book, item, or even a fourth level menu, can belong to more than one sub category, basically a sub cat or item with more than one parent, what do I do? Does this require multiple blank fields in one of the sub cat or book tables? Or, should this be implemented through using a primary key that encompasses many fields, or, even possibly should I have a field that declares what level menu the sub cat belongs in?
|
|
|
|
|
Put all categories in the same table and let the subcategories reference their parent categories like this:
CREATE TABLE Categories (
id NUMBER(8,0) NOT NULL,
parentid NUMBER(8,0) NULL,
name VARCHAR2(36) NOT NULL,
more stuff..
)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_pk PRIMARY KEY (id)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_id_fk FOREIGN KEY (parentid) REFERENCES Categories (id)
Then select the top categories with: select id,name from categories where parentid is null
And a subcategory with: select id,name from categories where parentid = <ID>
The self referencing foreign key blocks accidental deletion of a category that has subcategories
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Yes, exactly. 
|
|
|
|
|
Wow, I'm impressed. I had no idea it could be accomplished in so few of steps. Of course, I'm still trying to decode some of the "ADD CONSTRAINT" and REFERENCES language but I get the basic gist of what you're saying. I also had no idea that you could do the self referencing and thought, at minimum, it would require two tables. Very cool.
Thanks so much !!
|
|
|
|
|
I have a stored procedure defined thusly -- actual variable, database, and table names have been altered for NDA reasons:
CREATE PROCEDURE [dbo].[CountUniqueBirthdays]
@PersonID bigint
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CalendarDaysToSearch int
SET @CalendarDaysToSearch = 90
SELECT COUNT(DISTINCT o.Birthdays)
FROM
[MYDATABASE].[dbo].[PersonInfo] n,
[MYDATABASE].[dbo].[PersonInfo] o
WHERE
n.PersonID = @PersonID AND
--n.PersonID <> o.PersonID AND
-- all leads with...
n.KeyPerson=o.KeyPersonAND
-- within X days of current lead
o.CalendarDate between dateadd(dd, (-1 * @CalendarDaysToSearch ), n.CalendarDate ) and dateadd(dd, 0, n.CalendarDate )
END
For the sake of argument, assume this stored procedure is written correctly and is known to work. Now, I opened up VS2005 (my boss wants me to use it) and in the Data Sources window, I did the whole Add New Data Source/Data Source Configuration Wizard yada yada and added a TableAdapter to my DataSet for PersonInfo (not the real table name again).
I configured the TableAdapterto do a SELECT PersonId FROM PersonInfo and then also to call the Stored Procedure, CountUniqueBirthdays, above. ok, so i write the code to grab the records containing only the PersonIDcolumn and then I iterate over the column, passing each ID into the stored procedure one by one to check if the number of distinct persons with birthdays in a certain 90-day period is bigger than 1 -- AND DON'T ASK ME WHY DID I NOT JUST USE A CURSOR, CURSORS ARE SLOW AND WE HATE THEM, as below.
You know how you can open .xsd files in the Dataset Designer in VS2005? Well, I did, right-clicked the PersonInfo TableAdapter, clicked Add > New Query... and in the Add New Query Wizard I picked Existing stored procedure, and then configured CountUniqueBirthdays as the stored procedure to use.
<br />
try<br />
{<br />
Console.WriteLine("Querying the MYDATABASE.dbo.PersonInfo table...Please wait.");<br />
MYDATABASEDataSetTableAdapters.PersonInfoTableAdapter adapter =<br />
new MYDATABASEDataSetTableAdapters.PersonInfoTableAdapter();<br />
MYDATABASEDataSet.PersonInfoDataTable table =<br />
adapter.GetData();<br />
Console.WriteLine("Finished getting data.");<br />
<br />
Console.WriteLine("Running the CountUniqueBirthdays stored procdure. Searching for return values > 1...");<br />
foreach (MYDATABASEDataSet.PersonInfoRow row in table.Rows)<br />
{<br />
long? personID = row.PersonId;<br />
if (!personID.HasValue)<br />
continue;<br />
<br />
Console.WriteLine("Checking output of CountUniqueBirthdays for PersonId = {0}...", leadID);<br />
<br />
long? value = adapter.CountUniqueBirthdays(leadID);<br />
if (value.HasValue) {<br />
if (value.Value > 1)<br />
{<br />
Console.WriteLine("Value > 1 found for return from LQSCountVendor190Days...for <br />
leadId = {0}",leadID);<br />
break;<br />
}<br />
}<br />
}<br />
}<br />
catch (Exception e)<br />
{<br />
Console.WriteLine(e.Message);<br />
return;<br />
}
The output from the console app is:
<br />
Querying the MYDATABASE.dbo.PersonInfo table...Please wait.<br />
Finished getting data.<br />
Running the CountUniqueBirthdays stored procdure. Searching for return values > 1...<br />
Checking output of CountUniqueBirthdays for PersonId = 8752702...<br />
Specified cast is not valid.
Why? I followed what the IntelliSense had me do when I was typing in the stored procedure call! I don't get it, can somebody please help a newbie at Database programming?
Brian
|
|
|
|
|
The return type for CountUniqueBirthdays may not be long (or long? for that matter). Have you tried [int]? Or just receive it into an [object] variable and use Watch to see what it contains? i.e.
object value = adapter.CountUniqueBirthdays(leadID);
|
|
|
|
|
Thank you for your help. Yes, actually the IntelliSense was off and doing a Build Solution fixed it so that the IntelliSense told me the return type was object, and I was off and running! 
Brian
Sincerely Yours,
Brian Hart
|
|
|
|
|
I have changed the data type of the currencyID and countryID from NUMBER to INTEGER as number is not supported by dotnet.
When I run this query the data_type is still number.
SELECT C.Table_Name ,
C.Column_ID ,
C.Column_Name ,
C.Data_Type,
C.char_length
FROM ALL_TAB_COLUMNS C
WHERE C.TABLE_NAME = 'CURRENCY'
ORDER BY C.column_ID
Any idea how to refresh the ALL_TAB_COLUMNS view or can there be another reason for the change not showing.
Also - any suggestions as to the best Oracle support site. No it's not CP, we are very MS oriented.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In Oracle, Integer is simply Number(38). So if you create a column as an Integer it's converted to number(38)
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
When I look at the table in Schema view (toad) I see integer, if I script the table out I get integer, I'm betting if I used a parameter with a data type it would be integer as well. I have now found that if I create a new table the columns do not show up in the ALL_TAB_COLUMN view, seems it might be a permissions thing.
As NUMBER is not supported by dot net this seems very strange! Do all the orm tools convert number to integer/decimal.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I haven't worked in Toad so I can't say.
But an implicit conversion in dotnet is generally making a widening conversion to decimal unless the scale of the number is specifically 0 then it converts to integer.
If you want a different conversion you have to specify it in the parameters of the dataadapter/datareader.
Here's [^]more info on types.
And here http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055 is some info on the subject from Tom Kyte, who is an institution in the Oracle World
<edit> fixed link, kind of</edit>
"When did ignorance become a point of view" - Dilbert
modified on Monday, November 8, 2010 5:07 AM
|
|
|
|
|
Thanks for the links Jorgen, it looks like the orm will need to do some dancing around the number conversion in dotnet. Tom's link seems to have died but that could be b/c of my net nazi, I'm at work.
It seems like anything with no scale is an integer, precision seems to range from null to 22 for no discernable reason with both null and 22 being valid for an integer. Numbers in between have a scale and therefore decimal I guess.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It seems to me that the link to Tom's article is destroyed by the hamsters, so here it is as inline code:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
i want to insert values (into database table) which are calculated from an expression and is stored in a variable, in INSERT INTO statement, how can i do this?
i m doing this in VC++6 MFC using ODBC
constant values goes perfectally in database<
|
|
|
|
|
What you have tried about to insert value you have no mention whatever,
You can easily insert value into your database table by using
Insert into tablename values(your value depend upon table column data type)
|
|
|
|
|
|
for example here is some part of my code:
string str="Birthday";
i have to insert this str variable using:
INTERT into mytable VALUES(str) ;
how can i insert this???
n i dont know how to use parameters...plz tell me some very easy way to do this...
|
|
|
|
|
It partly depends on what language you host your SQL in, also on which database system you use.
INSERT INTO table (field0, field1 ...) VALUES (@Param0, @Param1 ... )
Then create parameters.
Then, as you loop through the data to insert, set the parameters and execute the statement.
|
|
|
|
|
I let you from the beginning step by step to insert data into table
Create a connection by using SqlConnection class as
SqlConnection con = new SqlConnection("connectionString")
then, you need to create Command object by SqlCommand Class as
string str = INTERT into mytable VALUES('"+str+"');
SqlCommand cmd = new SqlCommand(str,con);
then you need to execute query by using ExecuteNonQuery Method as
cmd.ExecuteNonQuery();
This is easiest way to insert data into table. Hope it will work for you.. 
|
|
|
|
|
thanks a lot RaviRanjankr! for ur concern...but i have tried this is a simple one...actually + sign is not recognized in the query, it gives syntex error...but n e ways thanks a lot my problem has been solved now,thanks
|
|
|
|
|
if you don;t want to use parameter then simple make Connction and Command and Execute your command it will save your data.
|
|
|
|
|
Please don't give people bad advice like this. You've basically just advised the poster that they should leave themselves wide open to a SQL Injection attack, and that's not cool.
|
|
|
|
|
|
|
i can't understand your comment? can you describe it?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
