grandlorie
Posts: 20
|
| Posted: 02/03/2010, 5:13 AM |
|
Hi All
I know this is not the right place to ask this, but i just want to get the right structure which will work well with CCS
i just need to start my project and got frustrated about the Database structure and i need an advice on what is the best structure should i go with.
my project is about ordering system and i start to categorize it like this:
tble_division
id_div (1,2,3,4,5,6,etc)
name_div (course, non-course, etc)
-----------------------
tble_subdivision
id_sub (1,2,3,4,5,6,etc)
name_sub (Java, Visual Basic, C#,etc)
id_div
-----------------------
tbl_items
id_ite (1,2,3,4,5,6,etc)
name_ite (Book, pdf, CD, etc)
id_sub
-----------------------
tbl_details
id_det (1,2,3,4,5,6,etc)
Code_det (Book123, Book254, CD524, CD856,etc)
price_det
instock_det
id_ite
-----------------------
so the user(who will place an order) page will look something like this:
Division: please select(drop down menu)
subdivision: please select(drop down menu dependent on Division)
Item: please select(drop down menu dependent on subDivision)
Code: please select(drop down menu dependent on Item)
This structure is working fine with me except one thing.
looking at the Items Table i can see repeated Items like Book, Book, Book, CD, CD, PDF,PDF,PDF) of course every item related to different division and subdivision and has different code but i am sure there is another way to structure this database without repeating the same items. am i right?
here is a Data Sample and as you can see it has repeated items.
=============================================
Division--------------SubDivision------------Item----------------Code------
=============================================
Non-Course---------Wood-------------------Material-------------ABC-Mat
Course--------------JavaScript--------------Book-----------------AMC_Bok
Course--------------Visual Basic------------Book-----------------SDE_Bok
Non-Course--------Lights-------------------Screw---------------SCR-SH10
Non-Course--------Lights-------------------Bulb-----------------ASI-5656
Course-------------JavaScript---------------PDF-----------------IDJ-9856
Course-------------Visual Basic-------------PDF-----------------LSO-852
Non-Course-------Wood--------------------Color----------------AJSIO-954
so any one can you please help?!?!?
Thanks
Grand
|
 |
 |
mamboBROWN
Posts: 1713
|
| Posted: 02/03/2010, 5:44 AM |
|
grandlorie
As a suggestion you might want to look at the example code (and database structure) from the online book store that Yes software included as a part of the CCS IDE. It will probably help you. Also, keep in mind you can also probably look at some Open Source projects to get a better idea about their database structures as well (Magenta and AssetsSoSimple just to name a few).
|
|
|
|
grandlorie
Posts: 20
|
| Posted: 02/03/2010, 6:01 AM |
|
Quote mamboBROWN:
grandlorie
As a suggestion you might want to look at the example code (and database structure) from the online book store that Yes software included as a part of the CCS IDE. It will probably help you. Also, keep in mind you can also probably look at some Open Source projects to get a better idea about their database structures as well (Magenta and AssetsSoSimple just to name a few).
Thanks but i already did a lot of research and could not find the answer i am after.
and this what i am still doing but hope there is someone here who can give me a hint.
Thanks
|
|
|
|
ReneS
Posts: 225
|
| Posted: 02/03/2010, 8:45 AM |
|
Hi,
I think your current structure is fine, each table has unique items so there is nothing double per table.
Sure in your output there are several books, however they are different books. But your tables seem normalized. You can use "group by" so you can get report like output etc. I think your fine, but i'm not a real expert.... Try searching for "database normalization" to get some more info about table structures.
Rene
|
|
|
|
grandlorie
Posts: 20
|
| Posted: 02/03/2010, 9:21 AM |
|
Quote ReneS:
Hi,
I think your current structure is fine, each table has unique items so there is nothing double per table.
Sure in your output there are several books, however they are different books. But your tables seem normalized. You can use "group by" so you can get report like output etc. I think your fine, but i'm not a real expert.... Try searching for "database normalization" to get some more info about table structures.
Rene
Yes my Database structure is right except for the Items Table
as you can see it has repeated items (Manual, PDF, etc).
and i am sure there is a way to make it better.
i know someone will show me the trick
Thanks
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 02/03/2010, 1:28 PM |
|
grandlorie
What is the table called for this data?
=============================================
Division--------------SubDivision------------Item----------------Code------
=============================================
Non-Course---------Wood-------------------Material-------------ABC-Mat
Course--------------JavaScript--------------Book-----------------AMC_Bok
Course--------------Visual Basic------------Book-----------------SDE_Bok
Non-Course--------Lights-------------------Screw---------------SCR-SH10
Non-Course--------Lights-------------------Bulb-----------------ASI-5656
Course-------------JavaScript---------------PDF-----------------IDJ-9856
Course-------------Visual Basic-------------PDF-----------------LSO-852
Non-Course-------Wood--------------------Color----------------AJSIO-954
Is it the orders table??
|
|
|
|
grandlorie
Posts: 20
|
| Posted: 02/03/2010, 1:45 PM |
|
Quote mamboBROWN:
grandlorie
What is the table called for this data?
=============================================
Division--------------SubDivision------------Item----------------Code------
=============================================
Non-Course---------Wood-------------------Material-------------ABC-Mat
Course--------------JavaScript--------------Book-----------------AMC_Bok
Course--------------Visual Basic------------Book-----------------SDE_Bok
Non-Course--------Lights-------------------Screw---------------SCR-SH10
Non-Course--------Lights-------------------Bulb-----------------ASI-5656
Course-------------JavaScript---------------PDF-----------------IDJ-9856
Course-------------Visual Basic-------------PDF-----------------LSO-852
Non-Course-------Wood--------------------Color----------------AJSIO-954
Is it the orders table??
Those are 4 different Tables (to include all the categories,sub-categories and the products details):
tbl_Division
tbl_subdivision
tbl_item
tbl_detail
and the user will use those tables to select the items he wanna order, so say the order page will look something like:
Division: please select
subdivision: please select
Item: please select
Code: please select
Quantity:
Date required:
Submit: Yes or No
and all these information will be entered into Orders Table(tbl_order) when submitted.
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 02/03/2010, 2:35 PM |
|
grandlorie
I justed noticed that in your TABLE: tbl_item you are storing the subdivision id. Why??
|
|
|
|
grandlorie
Posts: 20
|
| Posted: 02/03/2010, 2:46 PM |
|
Quote mamboBROWN:
grandlorie
I justed noticed that in your TABLE: tbl_item you are storing the subdivision id. Why??
That is right
If you look at the above 4 tables you ll notice that all of them are dependent drop down menus
so when the user select the Division from the tbl_division it will populate the subdivisions for that selection and when the user select the subdivision from tbl_subdivision it will populate only the items (on tbl_items) which related to that specific subdivision and finally when the user select the item from tbl_items it will populate the details to this specific item.
that is why i have to have the IDs on the way you see it above
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 02/03/2010, 8:29 PM |
|
grandlorie,
So basically an item is related to a subdivision which is related to a division. Based on this information you just proved why you will have duplicate items (in your table: tbl_items). The only way that you will be able to not have duplicates in your items table is if you create a normalized table between tbl_items and tbl_subdivision (which is the third form of normalization: http://www.isgdatabasedevelopment.com/database_normalization.html ). This way you can still track all items and the subdivision that they are related to. You can also use it for your dependent drop downs.
similar to this:
The KEY: Task = tbl_item ,Owners = tbl_subdivision, Projects2 = tbl_YouDecide
Hopefully this helps.
|
|
|
|
ReneS
Posts: 225
|
| Posted: 02/04/2010, 3:42 AM |
|
Quote :Yes my Database structure is right except for the Items Table as you can see it has repeated items (Manual, PDF, etc).
Sorry, but I disagree. The table itself does not have repeated items. You are building a hierachical tree, like this:
tble_division
- tble_subdivision
- tbl_items
- tbl_details
So, all unique values.
Then an order table containing OrderID and the fields you wish to use from the above tables.
Nothing wrong, no double input!
Unless I'm missing something.....
Rene
|
|
|
|
grandlorie
Posts: 20
|
| Posted: 02/04/2010, 5:28 AM |
|
Now it is more confusing than before
my mind is very much blank
|
|
|
|
datadoit
|
| Posted: 02/04/2010, 6:17 AM |
|
More fun reading during your endeavors:
http://en.wikipedia.org/wiki/Taxonomy
|
|
|
|
grandlorie
Posts: 20
|
| Posted: 02/04/2010, 6:47 AM |
|
Quote datadoit:
Thanks for that,
helped me to relax but not to solve the problem lol
|
|
|
|
grandlorie
Posts: 20
|
| Posted: 02/04/2010, 7:52 AM |
|
Quote ReneS:
Quote :Yes my Database structure is right except for the Items Table as you can see it has repeated items (Manual, PDF, etc).
Sorry, but I disagree. The table itself does not have repeated items. You are building a hierachical tree, like this:
tble_division
- tble_subdivision
- tbl_items
- tbl_details
So, all unique values.
Then an order table containing OrderID and the fields you wish to use from the above tables.
Nothing wrong, no double input!
You may be right, but when i search the Items (through List box) i can see it like:
Manual
PDF
Manual
Manual
Manual
PDF
PDF
PDF
and i do not think this is the right way to do..
i am just wondering if some one wanna do this database how they ll do it?
Unless I'm missing something.....
Rene
|
|
|
|
RoyBaird
Posts: 115
|
| Posted: 02/04/2010, 11:08 AM |
|
The table does have repeating data. To get the drop down to only show one occurance, use DISTINCT on you SQL statement that loads the dropdown. You might want to ORDER it on the same field so it is consistint and easy for the user to find what they are looking for.
_________________
Roy |
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 02/04/2010, 12:45 PM |
|
grandlorie
The bottom line is that no matter what you do you will have repeating data. What matters is how you choose to store it. It will work in your original layout or it will work with the suggestion that I made. In the end it is your decision.
|
|
|
|
ReneS
Posts: 225
|
| Posted: 02/04/2010, 2:07 PM |
|
Hi,
I don't see the repeated data....sorry if i'm not getting it...
tble_division
id_div (1,2,3,4,5,6,etc)
name_div (course, non-course, etc) (nothing repeating)
-----------------------
tble_subdivision
id_sub (1,2,3,4,5,6,etc)
name_sub (Java, Visual Basic, C#,etc) (nothing repeating)
id_div
-----------------------
tbl_items
id_ite (1,2,3,4,5,6,etc)
name_ite (Book, pdf, CD, etc) (nothing repeating)
id_sub
-----------------------
tbl_details
id_det (1,2,3,4,5,6,etc)
Code_det (Book123, Book254, CD524, CD856,etc) (nothing repeating)
price_det
instock_det
id_ite
I think i'm missing something... Maybe you are building the dependent listboxes from the "order" table? With that I mean the input table that uses all the fields from Division, subdivision, items and details. Then you should use "distinct".
Forgot, check your "joints", include all from Division and only those fields from subdivision where both fields are equal, then include all field from subdivision and only those fields from items where both fields are equal, then include all fields from items and only those fields from details where both fields are equal. (hierarchical)
Rene
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 02/04/2010, 7:44 PM |
|
ReneS
You are seeing it. It is just the way it is being explained (displayed). What is missing from the tbl_items is examples for the (subdivision) which would create duplicate name_ite. for example
id_ite-----name_ite-----id_sub
1------------ Book --------- 1 (Java)
2------------ Book --------- 2 (Visual Basic)
3------------ CD ------------ 3 (C#)
4------------ CD ------------ 1 (Java)
...etc
|
|
|
|
grandlorie
Posts: 20
|
| Posted: 02/05/2010, 2:46 AM |
|
Quote RoyBaird:
The table does have repeating data. To get the drop down to only show one occurance, use DISTINCT on you SQL statement that loads the dropdown. You might want to ORDER it on the same field so it is consistint and easy for the user to find what they are looking for.
untill we found a solution to this case (unless my structure is the right one). i think i should go with your solution.
under datasource type i choosed SQL
and under Data Source field i wrote this;
SELECT DISTINCT item_ite
FROM items_ite
ORDER BY item_ite ASC
which worked fine with listbox field withing the search form , but then the search button did not return any value.
also tried the same code with the List form it also worked fine but i lost the link next to the items to update.
????
|
|
|
|
grandlorie
Posts: 20
|
| Posted: 02/05/2010, 2:49 AM |
|
Quote mamboBROWN:
ReneS
You are seeing it. It is just the way it is being explained (displayed). What is missing from the tbl_items is examples for the (subdivision) which would create duplicate name_ite. for example
id_ite-----name_ite-----id_sub
1------------ Book --------- 1 (Java)
2------------ Book --------- 2 (Visual Basic)
3------------ CD ------------ 3 (C#)
4------------ CD ------------ 1 (Java)
...etc
You are right about that, this exactly what is happening, of course using DISTINCT will solve part of the problem but i do not know why i still feel that there is a way to do that with tables structures.
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 02/06/2010, 11:44 PM |
|
grandlorie
That is why I made the suggestion above. It will add another table to your database but it will take out the duplicate items in the tables tbl_items and track it in the new table. it would look like this:
tbl_items
--------------
id_ite-----name_ite
1------------ Book
2------------ CD
3------------ PDF
4------------ etc
tbl_newtable
-------------------
id_sub----------id_ite
1(Java) --------- 1(Book)
1(Java) --------- 2(CD)
3 (C#) ---------- 2(CD)
2(VB) ----------- 1(Book)
etc...
But again like I stated above the choice is yours.
|
|
|
|
grandlorie
Posts: 20
|
| Posted: 02/07/2010, 2:28 AM |
|
Quote mamboBROWN:
grandlorie
That is why I made the suggestion above. It will add another table to your database but it will take out the duplicate items in the tables tbl_items and track it in the new table. it would look like this:
tbl_items
--------------
id_ite-----name_ite
1------------ Book
2------------ CD
3------------ PDF
4------------ etc
tbl_newtable
-------------------
id_sub----------id_ite
1(Java) --------- 1(Book)
1(Java) --------- 2(CD)
3 (C#) ---------- 2(CD)
2(VB) ----------- 1(Book)
etc...
But again like I stated above the choice is yours.
Thanks a lot, i think this will do the job i ll try that.
thanks all for your help.
- i ll continue my project related next question on a nother thread.
cheers
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 02/07/2010, 9:21 AM |
|
grandlorie
If you can do me a favor and add [Resolved] or [Solved] to the title of this thread I would greatly appreciated it. Thanks.
|
|
|
|
|
