Designing relational tables

Hi, not sure if this is the right forum for this question.

I am creating relational tables for the first time in sql server express. I will have an orderItems table and an orders table. the MenuItems table is the problem. It is a catalogue of books. There will be about ten columns. all are unique to each book. i.e isbn number, title, author, publisher etc. but ten columns seems to be quite cumbersome. it may be easier to break the table down into two tables (i.e. primary details and secondary details perhaps) However to populate the table in the first place it would be easier to have it as one table instead of opening and closing 2 tables Adding the odd book to the two tables in the future would not be a problem. so the question is can i create a table and then brak it into two relational tables afterwards. If so how do i do this. this is my foirst go at relational tables and i am still trying to get a handle on visualising them. If my logic is up the wall please let me know...

Nick

Yes you can do this. Once you have your "big" table populated simply create the second smaller table and just execute an update statement:

update mySmallTable
set field1 = b.field1
, field2 = b.field2
, field3 = b.field3
from myBigTable b

|||

Great. Im really moving forward now. thanks for your time

Nick

Design Question

I am in the design phase of a relational database using OO methods...I have three groups of people that I want to track...1. Students 2. Parents 3. Adult Volunteers...

I have started with a table called people where I have attributes that are common to all three groups of people...then I have a students table and volunteer table that have attributes common only to a student or volunteer...the key in these tables is also the FK of the people table. I think this is proper design.

Here is my question:

There is a many-many relationship with parents and students. A third junction table is needed I know but how is this accomplished with subtype tables...would I create the juntion between parents table and Students table or between parents table and people table?

Any suggestion would be appreciated. Maybe this is all wrong and someone has a better solution...thanks

Tonypost what the current fields are in each table and the relationships between them.|||You have identified that Students, Parents and Volunteers are all types of people, so you have created a People table with a primary key (PeopleID). In the Student and Voluteer tables there are keys that contain PeopleID.

The task is to show the relationship between a student and its parents. Students and Parents are all sub-types of People. What you are trying to define is a relationship from one person to another, so the design should link a record from the People table to another record in the People table.

The Parent table will have a PeopleID column that represents the student and a second column with a renamed PeopleID to represent the ID of the parent. You can then add any other columns that contain parent data. The primary key will be a compound key based on the first two columns.

Referencing column 1 will list all the people who are the parents of Student A. Referencing column 2 will list all the students of parent B.

By linking people records in this way you can also represent the relationship where a parent is also a student.

As the table can be used to represent any relationship between two people records, you may want to check if any other relationships need to be represented (Student A is the brother/sister of Student B etc.) If so, you could add a RelationshipID column to the table, add it to the primary key and change the name of the table.

Whether this is the best solution for you depends on all of your requirements. Hope you found this useful.