Need help to define table relations so I can use ADO to create database

Question

0.00/5 (No votes)

See more:

Hello everyone!

I would like to start by saying thanks to everyone who takes some time to view this thread and try to help.

I have to create database with tables that have following characteristics:

1.) TableMaster( #PK_Master, ... , ~~NameOfBookStore~~ TypeOfGeothermalEnergy );

2.) ~~NameOfBookStoreTable~~ TypeOfGeothermalEnergy( ~~TotalAmountOfBooks~~ TotalAmountOfProbes[n], ~~TitleOfTheBook~~ DepthOfTheProbe[1] , ~~TitleOfTheBook~~ DepthOfTheProbe[2] , ... , ~~TitleOfTheBook~~ DepthOfTheProbe[n] );

The above "definition" is my pseudo-code, that tries to explain my problem.

This is my question:

How to turn that pseudo-code into real database relations ?

************************************

SIIDENOTE:

My personal opinion is, that I need to break the second table into 2 relations, I was thinking something like this:

TableMaster( #PK_Master, ... , ~~NameOfBookStore~~ TypeOfGeothermalEnergy );

~~NameOfBookStoreTable~~ TypeOfGeothermalEnergy( #( PK_Master, ~~OrdinalNumberOfTheBook~~ OrdinalNumberOfTheProbe ) , ~~TotalAmountOfBooks~~ TotalAmountOfProbes[n] );

HelperTable( #~~OrdinalNumberOfTheBook~~ OrdinalNumberOfTheProbe, ~~TitleOfTheBook~~ DepthOfTheProbe );

but I just don't have enough knowledge/experience with database design, so I need communities help with this.

That is all, again, I wish to thank anyone who tries to help.

***************************************

If any other information is required , please ask for it, I will more than gladly supply it.

Posted 18-Jun-13 16:06pm

MyOldAccount

Updated 18-Jun-13 16:32pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2013-06-18T16:13:00

Solution 1

I would expect I'd have a table for books. Then a table listing book stores. Then a table that has the id of a store, the id of a book, and the date it came in. That table would probably also store how much it cost. There would be a column for how much it sold for and the date it sold. Counting books that came in and have not sold, would give you the total books in stock.

OK, I still think it's similar. A table listing types of energy. A table listing probes. A table that links the two and stores the depth of the specific probe. An active flag so you can make some inactive, if you need to. Then you get the total count by doing a SUM query.

Posted 18-Jun-13 16:13pm

Christian Graus

Updated 18-Jun-13 16:35pm

v2

Comments

MyOldAccount 18-Jun-13 22:20pm

The names of the tables were used as an example, because I don't know how to properly translate their real names from my native tongue to English.

I apologize for that, and also I apologize for the confusion it made.

I will edit my question, so it makes no confusion anymore, in hope it will explain my problem better.

Again, I apologize for confusion, and I thank you for the reply.

Christian Graus 18-Jun-13 22:23pm

Don't apologise, I'm sorry if I misunderstood, I'm just trying to help, and I know that English is not your first language :-)

MyOldAccount 18-Jun-13 22:33pm

I have edited the question, hopefully it will help you.

Thank you for your help again!

MyOldAccount 18-Jun-13 22:45pm

Can you provide some pseudo code, or some short instructions ?

Maybe you can rework my pseudo code ?

I ask this, because, even though I understand your concept, I don't know how to turn your "words" into SQL statements.

Christian Graus 18-Jun-13 22:46pm

http://www.w3schools.com/sql/sql_create_table.asp

MyOldAccount 18-Jun-13 22:57pm

I know how to create table, I meant something like this:

TableMaster( #PK_Master, ... , TypeOfGeothermalEnergy );

TypeOfGeothermalEnergyTable( #( PK_Master, OrdinalNumberOfTheProbe ) );

ProbesTable( # OrdinalNumberOfTheProbe, DepthOfTheProbe );

Christian Graus 18-Jun-13 23:03pm

TypeOfEnergy( TypeId, Type)
Probe (ProbeId, OrdinalNumber, TypeId, Depth)

That works. You don't need a joining table because you don't have types of probe.

MyOldAccount 18-Jun-13 23:05pm

I will start analyzing this advice immediately and report my progress as soon as possible.

Thank you so much!

Christian Graus 18-Jun-13 23:06pm

Glad to help

MyOldAccount 20-Jun-13 1:19am

I think that this solution is what I needed, just tell me if I'm right for this:

TypeId is primary key in table TypeOfEnergy, and a foreign key for table Probe ?