Database Design Question/Problem
-
Since I am new to database, I want to know how to approch that. Assume that I have a table, I call it table A with the follwoing fields A | B | C | D ================================== Now, table A will take value from the fields like [A1], [B1], [C1], D[1] etc. Given that for each value added to field A, I want to create another table. For instance, for the above example, I will have a table A1 with fields like A11 | A12 | A13 =============== The same for A2 etc. The way to look at it, for each value added to field A of the original table, I want to have another table. I want to know if this is the best way to do it. I couldn't find any other way.
-
Since I am new to database, I want to know how to approch that. Assume that I have a table, I call it table A with the follwoing fields A | B | C | D ================================== Now, table A will take value from the fields like [A1], [B1], [C1], D[1] etc. Given that for each value added to field A, I want to create another table. For instance, for the above example, I will have a table A1 with fields like A11 | A12 | A13 =============== The same for A2 etc. The way to look at it, for each value added to field A of the original table, I want to have another table. I want to know if this is the best way to do it. I couldn't find any other way.
-
Since I am new to database, I want to know how to approch that. Assume that I have a table, I call it table A with the follwoing fields A | B | C | D ================================== Now, table A will take value from the fields like [A1], [B1], [C1], D[1] etc. Given that for each value added to field A, I want to create another table. For instance, for the above example, I will have a table A1 with fields like A11 | A12 | A13 =============== The same for A2 etc. The way to look at it, for each value added to field A of the original table, I want to have another table. I want to know if this is the best way to do it. I couldn't find any other way.
mfcuser wrote:
The way to look at it, for each value added to field A of the original table, I want to have another table.
No - You have a second table that has a foreign key that links it to A. You do not need multiple tables linked to A in this situation. Table A needs a primary key - this is a column (or group of columns) that uniquely identify a single row in table A. Table B needs a primary key (pretty much all tables need a primary key) and a foreign key. The foreign key has the same structure as the primary key of the table it links to (in this case table A). So, for example, if the PK of table A is an integer the foreign key on Table B that links to table A must also be an integer. When you insert rows in Table B that link back to Table A then you put into the foreign key the value of the primary key of the row in A that you want to link to. Does this help?
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
-
If the relation is 1 to n then it is ok Each item of table B would have an index refering to Table A item. But if it is an n to n relation, then you should have your two tables (A & B) and then a Table C with A & B indexes. Hope it helps.
-
If the relation is 1 to n then it is ok Each item of table B would have an index refering to Table A item. But if it is an n to n relation, then you should have your two tables (A & B) and then a Table C with A & B indexes. Hope it helps.
siskhoalanka wrote:
Each item of table B would have an index refering to Table A item.
No. An index will not help in this situation - it may affect performance, but it will not assist the joining of two tables. You need a foreign key on table B - and again with your example with the many-to-many join.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
-
Only the value from field A determine the next table that will be created. I think this is a 1:n
mfcuser wrote:
Only the value from field A determine the next table that will be created.
Creating multiple tables in this scenario will hurt the managability of your database. You can do it if you want, but it would create more problems than it solves.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
-
Since I am new to database, I want to know how to approch that. Assume that I have a table, I call it table A with the follwoing fields A | B | C | D ================================== Now, table A will take value from the fields like [A1], [B1], [C1], D[1] etc. Given that for each value added to field A, I want to create another table. For instance, for the above example, I will have a table A1 with fields like A11 | A12 | A13 =============== The same for A2 etc. The way to look at it, for each value added to field A of the original table, I want to have another table. I want to know if this is the best way to do it. I couldn't find any other way.
You may also consider that this type of question may be better asked in the SQL forum in future - You will get a greater number of people who know databases better than here - although the majority of C# developers will also know how databases work.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
-
You may also consider that this type of question may be better asked in the SQL forum in future - You will get a greater number of people who know databases better than here - although the majority of C# developers will also know how databases work.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
Thank you for your input, but I am a beginner in sql. The way to look at it, in a long term. Will not be manageable, but it is easier for me to understand. Again, lets look at it in terms of matrix Table A = [A1 A2 A3 A4]; Inside table A, we can have something like that A1 A2 A3 A4 =================== a b c d e f g h now I have two table Table a and table e with fields on their onw another way to look at it, if I have two table A and B. If I look at the contain of field A1 to enter data on table B. I cannot do that by row, because some of the field will be blank. Keep in mind I don't have much knowledge on database.
-
Thank you for your input, but I am a beginner in sql. The way to look at it, in a long term. Will not be manageable, but it is easier for me to understand. Again, lets look at it in terms of matrix Table A = [A1 A2 A3 A4]; Inside table A, we can have something like that A1 A2 A3 A4 =================== a b c d e f g h now I have two table Table a and table e with fields on their onw another way to look at it, if I have two table A and B. If I look at the contain of field A1 to enter data on table B. I cannot do that by row, because some of the field will be blank. Keep in mind I don't have much knowledge on database.
mfcuser wrote:
Again, lets look at it in terms of matrix Table A = [A1 A2 A3 A4]; Inside table A, we can have something like that A1 A2 A3 A4 =================== a b c d e f g h now I have two table Table a and table e with fields on their onw
I don't see a table a and table e - I see two rows where colum A1 has a value of "a" in one row and "e" in the other row.
mfcuser wrote:
if I have two table A and B. If I look at the contain of field A1 to enter data on table B. I cannot do that by row, because some of the field will be blank. Keep in mind I don't have much knowledge on database.
This confuses me even more because I have no idea what you are describing. Why not drop the A, B, etc. and use a real world example? It is less abstract and easier to understand.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
-
Since I am new to database, I want to know how to approch that. Assume that I have a table, I call it table A with the follwoing fields A | B | C | D ================================== Now, table A will take value from the fields like [A1], [B1], [C1], D[1] etc. Given that for each value added to field A, I want to create another table. For instance, for the above example, I will have a table A1 with fields like A11 | A12 | A13 =============== The same for A2 etc. The way to look at it, for each value added to field A of the original table, I want to have another table. I want to know if this is the best way to do it. I couldn't find any other way.
No, it's not a good database design. The way that you can easily see that it's not a good design, is that you are mixing the database design with the data in the tables. I'm not really sure what the values like "A1" would represent, but either you are putting table names in the data of the table A, or you are putting data in the table names. Anyway, either is bad. The result of mixing the database design with the data is that when you are adding data to the database, the database design has to change. It shouldn't need to do that. The only time you need to change the database design is when the purpose of the database changes. Changing the data in the database does not change it's purpose. You don't need separate tables for all the groups/sectons/classes/divisions/types/whatever, just put them in a single table with a field that is a foreign key to the A table.
--- b { font-weight: normal; }
-
mfcuser wrote:
Again, lets look at it in terms of matrix Table A = [A1 A2 A3 A4]; Inside table A, we can have something like that A1 A2 A3 A4 =================== a b c d e f g h now I have two table Table a and table e with fields on their onw
I don't see a table a and table e - I see two rows where colum A1 has a value of "a" in one row and "e" in the other row.
mfcuser wrote:
if I have two table A and B. If I look at the contain of field A1 to enter data on table B. I cannot do that by row, because some of the field will be blank. Keep in mind I don't have much knowledge on database.
This confuses me even more because I have no idea what you are describing. Why not drop the A, B, etc. and use a real world example? It is less abstract and easier to understand.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
I am just brainstorming the design. What I said, you have table A inside table A you have fields A, B, C, D. Now, inside field A, you have data A1, A2, A3 etc. You can call them rows as well. Now, I want to create new tables for those fields. For example, I will have three new tables: A1, A2, A3. The way to look at it, for each value in field A, I have a new table or you can call it for each row. I don't think this is a good design, but it is simpler for me to understand. This may have a similarity, but I don't hink it is the same as what I want to do. Assume that you have a table for customers; you also have a table for customer order. I can see there is a relationship between these two tables. This is what I really like to do, but I don't know if database allows you to do that. Can you have a table inside a table? If you look at it in a spreasheet form like a grid. You can click on one cell and have another grid open.
-
Since I am new to database, I want to know how to approch that. Assume that I have a table, I call it table A with the follwoing fields A | B | C | D ================================== Now, table A will take value from the fields like [A1], [B1], [C1], D[1] etc. Given that for each value added to field A, I want to create another table. For instance, for the above example, I will have a table A1 with fields like A11 | A12 | A13 =============== The same for A2 etc. The way to look at it, for each value added to field A of the original table, I want to have another table. I want to know if this is the best way to do it. I couldn't find any other way.
Locate the Northwinds sample MS Access version database and copy the database file in case or damage to original. If you have Microsoft Access application installed (part of MS Office), load the sample Northwinds database and look at the structure of the tables in design view. You will see that tables have primary and foreign keys. As you have copied the database file, it will not matter if you damage or corrupt it, so play with this sample database to familiarise yourself with it. For instance, a customer has a primary key (a unique reference) in the Customers table, when this customer place an order, the orders table must reference the customer by using a foreign key in the Orders table that is a direct relationship to the customers primary key in the customers table. The same rules are relevant when the Order-Details table relate to the Orders table which has a unique reference as a primary key. Also look at the queries for that sample database. These queries are essentially a way of getting data out (and in) of your database with meaningful information. For basic database on-line tutorials, visit the following http://www.htmlgoodies.com/primers/database/article.php/3478051[^] http://www.comptechdoc.org/independent/database/basicdb/index.html[^] http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials/[^] http://www.bkent.net/Doc/simple5.htm[^]
-
Locate the Northwinds sample MS Access version database and copy the database file in case or damage to original. If you have Microsoft Access application installed (part of MS Office), load the sample Northwinds database and look at the structure of the tables in design view. You will see that tables have primary and foreign keys. As you have copied the database file, it will not matter if you damage or corrupt it, so play with this sample database to familiarise yourself with it. For instance, a customer has a primary key (a unique reference) in the Customers table, when this customer place an order, the orders table must reference the customer by using a foreign key in the Orders table that is a direct relationship to the customers primary key in the customers table. The same rules are relevant when the Order-Details table relate to the Orders table which has a unique reference as a primary key. Also look at the queries for that sample database. These queries are essentially a way of getting data out (and in) of your database with meaningful information. For basic database on-line tutorials, visit the following http://www.htmlgoodies.com/primers/database/article.php/3478051[^] http://www.comptechdoc.org/independent/database/basicdb/index.html[^] http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials/[^] http://www.bkent.net/Doc/simple5.htm[^]
-
I am just brainstorming the design. What I said, you have table A inside table A you have fields A, B, C, D. Now, inside field A, you have data A1, A2, A3 etc. You can call them rows as well. Now, I want to create new tables for those fields. For example, I will have three new tables: A1, A2, A3. The way to look at it, for each value in field A, I have a new table or you can call it for each row. I don't think this is a good design, but it is simpler for me to understand. This may have a similarity, but I don't hink it is the same as what I want to do. Assume that you have a table for customers; you also have a table for customer order. I can see there is a relationship between these two tables. This is what I really like to do, but I don't know if database allows you to do that. Can you have a table inside a table? If you look at it in a spreasheet form like a grid. You can click on one cell and have another grid open.
mfcuser wrote:
What I said, you have table A inside table A you have fields A, B, C, D.
You cannot nest tables. You cannot have one table inside another table. You can have related tables. Table A is related to Table B.
mfcuser wrote:
Now, inside field A, you have data A1, A2, A3 etc.
You mean there are a number of rows, and for each row column A will contain data such as A1, A2, A3, etc. (Please try and pick up the correct terminology - it will make things much easier).
mfcuser wrote:
Now, I want to create new tables for those fields. For example, I will have three new tables: A1, A2, A3. The way to look at it, for each value in field A, I have a new table or you can call it for each row.
This is very unwise. As I have suggested before you have 2 tables. Table A and Table B. You filter the result of table B on its foreign key (which is the link to table A).
mfcuser wrote:
This is what I really like to do, but I don't know if database allows you to do that. Can you have a table inside a table?
No, you cannot - you relate them as I've mentioned previously. One customer can have many orders
Customer Table:
Id, FirstName, Surname, ....3, Joe, Bloggs, ....
5, Sarah-Jane Smith, ....CustomerOrder Table:
Id, CustomerId, OrderDate, DispatchDate, ....1, 3, 2006-12-01, 2006-12-06, ....
2, 5, 2006-12-02, 2006-12-06, ....
3, 3, 2006-12-06, -null-, ....We see here that Joe Bloggs has made 2 orders. They can be joined together like this:
SELECT FirstName, Surname, OrderDate, DispatchDate
FROM Customer
INNER JOIN CustomerOrder ON Customer.Id = CustomerOrder.CustomerIdwhich will return
FirstName, Surname, OrderDate, DispatchDate
Joe, Bloggs, 2006-12-01, 2006-12-06
Sarah-Jane, Smith, 2006-12-02, 2006-12-06
Joe, Bloggs, 2006-12-06, -null-
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or
-
Locate the Northwinds sample MS Access version database and copy the database file in case or damage to original. If you have Microsoft Access application installed (part of MS Office), load the sample Northwinds database and look at the structure of the tables in design view. You will see that tables have primary and foreign keys. As you have copied the database file, it will not matter if you damage or corrupt it, so play with this sample database to familiarise yourself with it. For instance, a customer has a primary key (a unique reference) in the Customers table, when this customer place an order, the orders table must reference the customer by using a foreign key in the Orders table that is a direct relationship to the customers primary key in the customers table. The same rules are relevant when the Order-Details table relate to the Orders table which has a unique reference as a primary key. Also look at the queries for that sample database. These queries are essentially a way of getting data out (and in) of your database with meaningful information. For basic database on-line tutorials, visit the following http://www.htmlgoodies.com/primers/database/article.php/3478051[^] http://www.comptechdoc.org/independent/database/basicdb/index.html[^] http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials/[^] http://www.bkent.net/Doc/simple5.htm[^]
I don't know much about database but this is somthing similar in terms of keys relationship. Let me know if I am right. Assume that I have a table name customer than inside that table I have the following fields. CustomerID FirstName LastName PhoneNUmber Address City Zipcode Insde that table I can have a customer name Jhon, Gerry, Fred, Tony Now, I have another table name Order, insider that table I have OrderNumber Price Quantity Total I don't know a lot about database, but I assume there is a relationship between CustomerID and OrderNumber. Now assume that Gerry order 1 Item, based on the following OrderNumber = 320; Price = $2 Quantity = 1 Total = $2 How many relationship to I have? I assume one, I also think that Gerry oder number can be retrieved from his name, order number or customer number, etc. Basically, I want to do something similar to that, for example, for one person I can have multiple order. What is different from mine, is that all the fields from the second table do not need to be filled. Some of them can be empty.
-
I don't know much about database but this is somthing similar in terms of keys relationship. Let me know if I am right. Assume that I have a table name customer than inside that table I have the following fields. CustomerID FirstName LastName PhoneNUmber Address City Zipcode Insde that table I can have a customer name Jhon, Gerry, Fred, Tony Now, I have another table name Order, insider that table I have OrderNumber Price Quantity Total I don't know a lot about database, but I assume there is a relationship between CustomerID and OrderNumber. Now assume that Gerry order 1 Item, based on the following OrderNumber = 320; Price = $2 Quantity = 1 Total = $2 How many relationship to I have? I assume one, I also think that Gerry oder number can be retrieved from his name, order number or customer number, etc. Basically, I want to do something similar to that, for example, for one person I can have multiple order. What is different from mine, is that all the fields from the second table do not need to be filled. Some of them can be empty.
mfcuser wrote:
How many relationship to I have?
Zero - You have not set up a relationship between Customer and Order. The Order table needs to know to which customer each order refers. You must add a CustomerId to the Order table.
mfcuser wrote:
What is different from mine, is that all the fields from the second table do not need to be filled. Some of them can be empty.
That is fine - it isn't a big deal, just make the columns nullable.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
-
mfcuser wrote:
What I said, you have table A inside table A you have fields A, B, C, D.
You cannot nest tables. You cannot have one table inside another table. You can have related tables. Table A is related to Table B.
mfcuser wrote:
Now, inside field A, you have data A1, A2, A3 etc.
You mean there are a number of rows, and for each row column A will contain data such as A1, A2, A3, etc. (Please try and pick up the correct terminology - it will make things much easier).
mfcuser wrote:
Now, I want to create new tables for those fields. For example, I will have three new tables: A1, A2, A3. The way to look at it, for each value in field A, I have a new table or you can call it for each row.
This is very unwise. As I have suggested before you have 2 tables. Table A and Table B. You filter the result of table B on its foreign key (which is the link to table A).
mfcuser wrote:
This is what I really like to do, but I don't know if database allows you to do that. Can you have a table inside a table?
No, you cannot - you relate them as I've mentioned previously. One customer can have many orders
Customer Table:
Id, FirstName, Surname, ....3, Joe, Bloggs, ....
5, Sarah-Jane Smith, ....CustomerOrder Table:
Id, CustomerId, OrderDate, DispatchDate, ....1, 3, 2006-12-01, 2006-12-06, ....
2, 5, 2006-12-02, 2006-12-06, ....
3, 3, 2006-12-06, -null-, ....We see here that Joe Bloggs has made 2 orders. They can be joined together like this:
SELECT FirstName, Surname, OrderDate, DispatchDate
FROM Customer
INNER JOIN CustomerOrder ON Customer.Id = CustomerOrder.CustomerIdwhich will return
FirstName, Surname, OrderDate, DispatchDate
Joe, Bloggs, 2006-12-01, 2006-12-06
Sarah-Jane, Smith, 2006-12-02, 2006-12-06
Joe, Bloggs, 2006-12-06, -null-
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or
-
mfcuser wrote:
How many relationship to I have?
Zero - You have not set up a relationship between Customer and Order. The Order table needs to know to which customer each order refers. You must add a CustomerId to the Order table.
mfcuser wrote:
What is different from mine, is that all the fields from the second table do not need to be filled. Some of them can be empty.
That is fine - it isn't a big deal, just make the columns nullable.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
I get you. The relationship is not setup yet, but it can be setup. I don't have access in my machine to try some examples. In the diagram view of visual studio, assume that I have the two table. If I drag customerid to order number, does that establish a relationship automatically?
-
I get you. The relationship is not setup yet, but it can be setup. I don't have access in my machine to try some examples. In the diagram view of visual studio, assume that I have the two table. If I drag customerid to order number, does that establish a relationship automatically?
mfcuser wrote:
If I drag customerid to order number, does that establish a relationship automatically?
It would pop up a dialog to confirm which columns from each table are used to form the relationship. However, a relationship with customerId on one end with Order number on the other would not work because they are not the same thing. You must have the same keys on both ends of the relationship. One is the primary key on the parent table, and the other is a foreign key on the child table. You must have CustomerId (or something that represents customerId on both tables). So the CustomerID of the Customer table is used to form a relationship with CustomerID on the Order table. There will be many rows in the Order table with the same CustomerID.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
-
mfcuser wrote:
If I drag customerid to order number, does that establish a relationship automatically?
It would pop up a dialog to confirm which columns from each table are used to form the relationship. However, a relationship with customerId on one end with Order number on the other would not work because they are not the same thing. You must have the same keys on both ends of the relationship. One is the primary key on the parent table, and the other is a foreign key on the child table. You must have CustomerId (or something that represents customerId on both tables). So the CustomerID of the Customer table is used to form a relationship with CustomerID on the Order table. There will be many rows in the Order table with the same CustomerID.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
I am looking at the Northwind database. When I open the category table, it brings a table which looks like table inside table. That may not be the way to say it. I believe that may be based on relationship. When I clicked on the plus sign, it opened a child table, then when the child table open, I clicked on the plus sign again, then it opened another child table. I assume that is because of the relationship.