Junction Table Design Options

Jul 20, 2005

As an example, I am building an authentication mechanisim that will use
data in the 3 left tables to determine rights to objects in a
destination table, diagrammed below. In this structure, multiple
records in the left tables will point to multiple records in the right
table. Normally, I would approach this problem using junction tables
(LeftID, RightID) to create many-to-many joins.

However, given the structure of each table is nearly identical (as far
as the linking IDs are concerned), I could also use a single junction
table with columns for each available table ID (LeftID1, LeftID2,
LeftID3, RightID). In this table, only two IDs would be utilized per
row (LeftIDx -> RightID).

In both designs, the needed rights information is returned from fairly
simple views, thus the end result is equivalent. The advantage to the
second, multi-ID junction table design, is a simpler database
structure. However, never using this approach before, I am unsure of
the potential future performance impacts.

Any significant downsides to this second design? Examples of an
abbreviated structure follow:

Data Tables
-----------
LeftTable1
LeftID1 (int)
Data1

LeftTable2
LeftID2 (int)
Data2

LeftTable3
LeftID3 (int)
Data3

DestinationTable
RightID (int)
Data


Linking tables option 1
-----------------------
JunctionTable1
LeftID1
RightID

JunctionTable2
LeftID3
RightID

JunctionTable3
LeftID3
RightID


Linking table option 2
----------------------
Junction
ID1 (int)
ID2 (int)
ID3 (int)
DestinationID (int)

View 1 Replies


ADVERTISEMENT

Design Question Regarding Junction Tables

Jan 18, 2008

I'm designing a database with 3 tables called Function, Test andScene.A Function has multiple Tests, but a Test has only one Function. Amany to many relationship exists between Test and Scene therefore Ineed a junction table between these two tables - giving 4 tables intotal. The Test table would store a foreign key, the primary key ofthe Function table.There is a problem with design though and that is that Functions andScenes are actually defined before the Test is defined. Therefore itshould be possible to create a Function and add to id its Scenes,before Tests have been defined. In other words, Scenes are as much apart of a Function as they are of Tests. Tests are in fact only ofrelavence to testers. Anyway, to satisfy this scenario, a Junction boxis also needed beween Function and Scene. This creates a loop betweenall tables.Is this a good approach? Any other suggestions or advice on thematter? Any advice regarding data integrity?Thanks,Barry

View 1 Replies View Related

Junction Table SQL Sentence - Please Help Me Out

Feb 17, 2006

I have two tables. A table called users (content speaks by it self) and a table called groups.
Since i want every user to be able to be a member of several groups and, of course, a group to have several users i have made a junction table called jt.
The junction table contains userId's and groupId's according to the user-group bindings. The primary key(identity) in the junction table is a int named jtId.
Now i want to take out all posts from the junctiontable and the corrresponding userName (s) from the users-table and the corresponding groupName from the groups-table.
Can somebody please help me to make a SQL command that will di that for me. I have tried with INNER JOIN and several SELECTS in the same command.
Thanks in advance, Greetings from Esben

View 4 Replies View Related

Junction Table/ Many To Many Relationships

May 9, 2007

I am trying to update 2 tables at the same time by adding new records to them and then making sure that they are related on my junction table?



Table1

packageID

Packagename



Table2 (JunctionTable)

PackageID

JobID



Table3

JobID

JobName



And I want to create a new package and new Job Name at the same time I would need to make multiple insert statements



First take care of the new Package

INSERT INTO Table1 (PackageName) Value (@PackageName)


Then Take Care of the JobName

INSERT INTO Table3 (JobName) Value (@JobName)



Finally marry the two together

but how?



View 7 Replies View Related

INSERT Among 3 Tables And 1 Is A Junction Table

Sep 23, 2007

Looking for some help here, so thanks for any input. I'm a painfully new newbie to SQL scripting.Situation:
I have a simple database to handle an organization's events. Those
events are categorized and may have more than one category assigned to
each event. I need a maintenance Web Form to update their events.Set
Up (so far): I have a CATEGORIES table. It has an auto incrementing UID
and a Category name field. This table will be updated so infrequently,
I plan to update it manually (no need for a maintenance Web Form). Next
is the EVENTS table. It also has an auto incrementing UID along with
several fields (Title, Location, DateTime, etc.). The junction table is
named jEVENTSCATEGORIES. It has its own auto incrementing UID along
with 2 fields named for the primary keys (UIDs) in the other 2 tables
(EventsID and CategoryID).Goal: On the Web Form, I have a
CheckBoxList control that's populated by the CATEGORIES table. One or
more categories can be checked for each event. I have a FormView
control that allows Edits and Inserts.Need: I need to know the
INSERT statement(s) required to insert a new record in the EVENTS table
and then to update one or more rows in the junction table
(jEVENTSCATEGORIES).My Assumptions: I know how to create
SELECTs and INSERTs and whatnot, but I'm not certain how to create a
second INSERT statement that is based on a variable (or output) from a
previous action. So any help would be MUCH appreciated. Thanks for your
time!

View 7 Replies View Related

Retrieving Data From A Junction Table

Jan 30, 2008

Hi i have a junction table(UserGroups) which is linking my users table with my groups table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change




UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: MUFC

UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC
Below is my SQL statement; 
SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')

View 6 Replies View Related

SQL 2012 :: Junction Table In Many To Many Relationship

May 5, 2014

Why do we need a junction table in a many to many relationship? Why can't everything be in just 2 tables?

View 9 Replies View Related

Stopping Duplicate Values From A Junction Table

Feb 28, 2008

Hi i have a junction table(UserGroups) which is linking my "users" table with my "groups" table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change.




UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: MUFC

UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC
Below is my SQL statement; 
SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')

View 11 Replies View Related

Stopping Duplicate Values From A Junction Table

Mar 1, 2008

Hi i have a junction table(UserGroups) which is linking my "users" table with my "groups" table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change.

UserName: Edwin Carols
UserAge: 28
JobTitle: Manager
GroupName: MUFC


UserName: Edwin Carols
UserAge: 28
JobTitle: Manager
GroupName: AFC


Below is my SQL statement;


Code:

SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')



And my table structure is like;

Users; UserID, UserName, UserAge
Groups; GroupID, GroupName
UserGroups; UserGroupID, UserID, GroupID

View 3 Replies View Related

Junction/link Table Primary Key: Best Practice Using SQL Server

Nov 30, 2005

Hi all,
This is a bit of a general question regarding SQL Server link tables that i hope someone can find the time to answer.

I am looking for the best practice. If I have 3 tables

1) tblCompetition
2) tblTeams
3) tblTeamsInCompetition (this is the link table)

I know the link table should have a related CompetitionID and TeamID,
but should the link table also have a primary key that is an identity
autoincrement by 1 just as you would with the other two tables. In
Access I have never done this, however in SQL Server I have read that
you should do this (but now I can't find the documentation again which
prompts me to ask the question here).

Thanks for taking the time to answer this question.

View 7 Replies View Related

Insert Statement For Junction Table On Many To Many Relationship Ms Sql Server 2005

Oct 10, 2007

Hello,

This seems like such a simple problem but I am new developer even through I have been on the administration end of things for some time. I will go into more detail about my tables and there relationships below. Anyway, I am trying to create a many-to-many relationship within ms sql server 2005. I have created both of my primary tables and also a junction table per the directions on microsoft's website all per ms's instructions as stated here...

http://msdn2.microsoft.com/en-us/library/ms178043.aspx

At then end of these instruction it states as a NOTE: The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. For information about inserting data into a table, see How to: Create Insert Results Queries (Visual Database Tools).

http://msdn2.microsoft.com/en-us/library/ms189098.aspx

and these directions do not go into detail on how to do an insert on a junction table. And I cant find out how to do this anywhere on the internet... I did create a T-SQL INSERT statement in a trigger as listed below but I end up getting an error AS LISTED BELOW....

Here is how I set everything up...

PetitionSet table consists of:

PetitionSetID int auto-increment primary key
PetitionSetName varchar(50) no nulls
PetitionSetScope varchar(50) no nulls


the Petition table consists of:

PetitionID int auto-increment primary key
PetitionSetID int no nulls
PetitionName varchar(50) no nulls


the SetToPetitionJunction table consists of:
PetitionSetID int
PetitionID int

And, there is a composite key made up of both the PetitionSetID and PetitionID fields.

I have created the foreign key relationships with DEFAULT VALUES from the SetToPetitionJunction table to each column's respective corresponding column in each of the tables: PetitionSet and Petition.


The trigger is on the Petition table and it has the following code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER .[SetToPetitionJunctionTrigger]
ON .[dbo].[Petition]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO SetToPetitionJunction
(PetitionID, PetitionSetID)
SELECT Petition.PetitionID, PetitionSet.PetitionSetID
FROM Petition INNER JOIN
PetitionSet ON Petition.PetitionSetID = PetitionSet.PetitionSetID

END

I have created an asp.net 2.0 front end to insert values into the PetitionSet table and the Petition Table. And in the detailsview for the Petition table I manually insert the PetitionSetID field to the number that corresponds to an auto-generated number on the primary key of the PetitionSet table. So I am maintaining referential integrity...

The first time it works and inserts one record in the Junction table containing the PetitionSetID from the PetitionSet table and the PetitionID from the petition table.

Then when I try to add in another petition for the same petition set number just like I did the first time and then I get this error...

Violation of PRIMARY KEY constraint 'PK_SetToPetitionJunction'. Cannot insert duplicate key in object 'dbo.SetToPetitionJunction'.
The statement has been terminated.



David

All Rights Reserved in All Media







View 3 Replies View Related

SQL Query Question : How To Add Data To Two Tables Connected By Third Junction-table.

May 1, 2008


Hello specialists.

Maybe this is the wrong formum but I've got a question for which you probably have the answer, i hope.


Situation
------------
John is member of Group_A and Group_B
Bill is member of Group_B and Group_C
Allison is member of Group_A and Group_E

How can I create a query to input Allisons username into table 1 and groupmembership into table 2. Also updating the relationship within junction-table3 must be done automaticaly. I want to avoid duplicate records.
The final situation I want is given in red text.

The relationships between the tables are as follows
-------------------------------------------------------------
Table1 (PK)ID-Userinfo [ONE] <------------> [MANY] Table3 ID-Userinfo
Table3 (PK)ID-GroupInfo [MANY] <------------> [ONE] Table2 (PK)ID-GroupInfo

Table1: UserInfo
------------------------------
(PK)ID-Userinfo UserName
1 John
2 Bill
3 Allison

Table2: GroupInfo
------------------------------
(PK)ID-GroupInfo GroupName
1 Group_A
2 Group_B
3 Group_C

4 Group_E

Table3: MemberOf
------------------------------
(PK)ID-MemberOf ID-UserInfo ID-GroupInfo
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 4


I hope you can help me cracking this nut.

Thx in advance. Greetings Fred

View 7 Replies View Related

How To Write A Select Query To Return Records In A Many-to-Many Relationship - Junction Table???

Oct 26, 2006

Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!  

View 6 Replies View Related

Table Options

Aug 14, 2007

Hi all,

This follows on from a query I had a few days back (and for which I was promptly flamed! However, I've got skin like a rhinoseros, so here goes...)

I have a table - ProjectSite - that is pulling information from a two tables (Project and Site). This table contains data regarding which sites are part of which project.

I now want a means of reporting dates against this. The problem is that each project has bespoke milestone dates, so I can't just create columns in ProjectSite. The only solution I can see is to pull each project (and there's quite a few and its corresponding sites into a new table) and then I can create my bespoke columns.

Does this sound like the best viable option, or can anyone suggest another means of doing this?

TIA,

SamuelT

View 1 Replies View Related

DB Design :: Table Design For Packages

Aug 18, 2015

I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show.  How to design the table.. 

Product name   :  
Nestle milk |
Rainbow milk
packages  :
CTN,OTR, NOs |

CTN, NOs
Price:
50,20,5 |
40,6

(Remarks for your reference):CTN=10nos, OTR=4 nos  
| CTN=8 Nos

View 3 Replies View Related

SQL Server 2014 :: View To Physical Table Options?

Jun 18, 2014

I have a view which select some few columns from multiple tables with where clause and have 5 unions of different category of data.

For the best performance i need to change this to physical table or any other options which can increase my performance.

View 2 Replies View Related

SQL Server 2012 :: A Valid Table Name Is Required For In / Out Or Format Options

Apr 24, 2015

Why am I getting message "A valid table name is required for in, out, or format options."

I used the syntax from a tutorial about bcp utility. I am trying to create a format file for flat file import and export.

My server instance is "stat-hpsqlexpress"

The database name is "STATRLO"

Owner is "dbo"

Table name is "PM-allactivity-emaillog_042315"

The bcp comand I am trying to run is:

bcp STATRLO.dbo.PM-allactivity-emaillog_042315 format nul -c -t, -f C:databaseActivity_c.fmt -S stat-hpsqlexpress - T

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
SQL Server Version:
Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
Jul 22 2014 15:26:36
Copyright (c) Microsoft Corporation
Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Yes I know the instance says sqlexpress...it was upgraded.

View 3 Replies View Related

SQL Server 2008 :: System Table In Which Job Notification Options Saved

Jun 14, 2015

In the notification tab of job properties, I can see three options in the drop down list at the right hand side.

When the job succeeds
When the job fails
When the job completes

In which system table these options are saved ? Is it possible to add a custom option in that dropdown list.

View 2 Replies View Related

Help Needed: Granting Create Table Permisions On Specific Schema Options

Sep 6, 2007

Having some trouble getting my head around setting access to specificschemas- here's my problem:I've created a specific schema that I only want certain users tocontrolProblem: Even though I give them full access....the cannot createtables under that schema...my code is below (flyer is the schema,eflyerAdmin is the role, and eflyer is the user):GRANTALTER,CONTROL,DELETE,EXECUTE,INSERT,REFERENCES,SELECT,TAKE OWNERSHIP,UPDATE,VIEW DEFINITIONON SCHEMA::flyerTO eflyerAdminGO-- Add an existing user to the roleEXEC sp_addrolemember N'eflyerAdmin', N'eflyer'

View 1 Replies View Related

DB Design :: Insert / Update FACT Table From Staging Table

May 6, 2015

We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region.  this process is schedule,  every 5 min job is run and Update fact table.but time of Insert and Update too long from  staging  to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number  how many region we need to update and at a time single Region we are updating using while loop in current SP.

View 7 Replies View Related

DB Design :: Table Partitioning Using Reference Table Data Column

Oct 7, 2015

I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster? 

I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)

View 3 Replies View Related

DB Design :: Insert Data From One Table To Another Table

Jul 30, 2015

I have to tables like given below Landing table "A" (Data load will happen over here, No primary keys mentioned over here) table "B" .Now I want to move the data from A to B.I have made use of below query insert into B select * from A...Landing table "A" has huge no of records, MS SQL server is taking huge amount of time.any alternative way to make this insertion process faster?

View 6 Replies View Related

Table Design

Apr 30, 2008

Hi, I am developing an application to a garment factory. I have a doubt in designing a table.Basic tables:Jobs, JobColors, Material, Units, Currencies ...These tables are designed with normalization rules.I got a problem at PurchaseOrderDetailsMain
table is JobMaterial. It has materialid, jobid, supplierid, description
and TypeFactor(which represents the type of order) means that the
material is ordered based on size or colors or total qty.1 for ByColor, 2 for BySize, 3 for ByQty, 4 for By Contrast colorsThe main problem at the details of the sub table.JobMaterialDetailsIf typefactor is by size, i need to store the details based on sizeex: S - 2000pcs, M - 4000pcs, L - 4000pcs, XL - 2000pcsSo I will have 4 records per each sizeIf it is by color, White - 3000pcs, Portabella - 5000pcs, Black - 2000pcs.If it is by general, Total qty 10000pcsHow
can I design this table. If I take, ColorOrSize column, it will refer
different values for diffrent typefactor. When by size, it will have
Size and when by color, it will refer colorcode.But colors are having referential integrity. So it is violated other than by color as typefactor.What is the best way to design this table?Can anybody suggest?Thanks in advance

View 2 Replies View Related

Table Design

Jul 24, 2000

I am desingning a table and i have a column OrderID and another column call Order, is neccessary to use a primary key, because One OrderID may have many Orders?
Thanks.

View 5 Replies View Related

Table Design

Aug 21, 2000

Coming from a support background and having to design my first database I have a couple of questions re- table design. Firstly I have set up several tables and included one field (of the same name) in each. This is a primary key in one table with an incremental seed. I would like this info to appear in the other tables although these can be duplicates in the other tables. How is it best to achieve this relationship. From reading it suggest FK in the relationship application but looking at other databases this seems to have been achieved by some other means. Is it more common to use stored procedures to enforce this? If so please add pointers. Secondly, I have set up a couple of master tables to act as looks ups for fields in other tables. Again how do I get this to look up the table - is it done through stored procedures or at the time of writing the front end application?? Sorry if this is all basic stuff but it is new to me.

Thanks for any help

View 1 Replies View Related

Table Design

Oct 26, 2004

CREATE TABLE [dbo].[table1] (
[aaa] [bigint] IDENTITY (10000, 1) NOT NULL ,
[bbb] [int] NOT NULL ,
[ccc] [int] NOT NULL ,
[ddd] [bigint] NOT NULL ,
[eee] [int] NOT NULL ,
[ffff] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[gggg] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hhh] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iii] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jjj] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[kkk] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lll] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mmm] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[nnnn] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ooo] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ppp] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qqqq] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rrrr] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ssss] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tttt] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uuuuu] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vvvvv] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wwwww] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[xxxxx] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[yyyyy] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zzzzz] [int] NULL ,
[abc] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[def] [datetime] NULL ,
[ghi] [datetime] NOT NULL ,
[jkl] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mno] [bigint] NULL
) ON [PRIMARY]


I have created a table with above column width. The rowsize is more than 8kb.And the table holds millions of rows of data. So is it a correct way of designing the table?
Or how can I redesign this table.

Thanks.

View 1 Replies View Related

Table Design Help

Mar 16, 2004

I'm currently developing a real estate system to manage order processing and work flow. I'm a little uncertain as to how to design the tables because an order can have N number of applicants, owners, buyers, and properties. There are cases where there are 9 different buyers and some where the number of properties exceeds 20. It seems that normalization might make the situation crazy, but I'm a touch rusty. Thanks.

View 2 Replies View Related

Table Design ? What To Do?

Apr 16, 2004

I am developing an estimating application and at the "quantity takeoff" level, all products/equipment/sub contracting/etc.... end up on the same table because of the common elements:


Item | Quantity | Unit Price | SubTotal | Total


Now, since the unit price of a product is not determined from the same tables as the unit price of equipment rentals or sub contract work, etc...
how do I direct the attention to the appropriate table when trying to obtain the unit price or edit the unit price etc...

If my question is not clear, let me know, I will try to clarify.

Mike B

View 3 Replies View Related

Design Table

May 10, 2008

Hi,

Is there a way to copy and save my Design Table? I want to transfer it to another workstation.

View 8 Replies View Related

Table Design

Feb 13, 2006

Hi am trying to figure out my table design need some advice

I have a table that catches the telephone numbers of a customer , there are 5 telephones for each customer. All the numbers are there in the DB

Should I create the design in the following format

CustomerName
Tel1
Tel2
Tel3
Tel4
Tel5

Or Should I have

Customer
Telephone

& then have 5 rows for each customer

Please advice


I even wanted to knw how should the design be , if all the Tel Nos are not captured, I mean what if some customers have 3 tel Nos, while some may just have 1 while some may have all 5

Thanks

View 7 Replies View Related

Please Help With SQL Table Design

Aug 21, 2006

Hi I'm having trouble figuring out this database design and normalising the data. Following are the tables and I am wonder if it can be normalised any further. This is a database of Architects and their work history and building they have designed.

Architects:
FirstName
LastName
Gender
DOB
Bio

Works:
Title
Client
Type_of_Building
Style
Address
Year_Designed
Year_Completed
Comments
Architect_ID

Firm: (this is the various firms that the architect has worked for)
CompanyName
Address

EmploymentHistory: (when the architect worked with a particular firm)
Year_Start
Year_End
Firm_ID
Architect_ID

Publication: (where the architects works were published)
Author
Date_Publication
Title
Source
Publisher
Architect_ID

I would really appreciate some help and suggestions on creating the relationships.
thanks

View 5 Replies View Related

Table Design

Sep 17, 2007

I create following tables.

employee table
------------------------
EMP_ID varchar
NAME varchar
DEPARTMENT_CODEint
POSITION_CODEint
COUNTRY_CODEint


department table
------------------
DEPARTMENT_CODEint
DEPARTMENT_NAMEvarchar

position table
---------------
POSITION_CODEint
POSITION_NAMEvarchar

country table
-------------
COUNTRY_CODEint
COUNTRY_NAMEvarchar


In employee table I have to store two country names.
ie. employee currently working in this country
but his home country name (origin country) is another
name. In country table all countries information available.
ie. country_code and country_name.

How I can design the employee table that it get country_name from COUNTRY TABLE ?
but we can not use same country_code in two fields.

OR
otherwise I will have to create employee table like this

employee table
------------------------
EMP_ID varchar
NAME varchar
DEPARTMENT_CODEint
POSITION_CODEint
COUNTRY_CODEint
COUNTRY_NAMEvarchar

ie. during data entries direct insert the country_name in employee
table ?

I want to create separate table for separate data.
department,postion,country.

How I can use unique country_code in employee table's two
fields ?

regards
Martin

View 2 Replies View Related

Best Table Design

Nov 8, 2007

Hi,

I'm struggling coming up with an optimal table design for the following scenario.

I have a table in which each row is recording information about a document - author, publisher, publication, title, abstract and so forth. I also have an image table which contains information about the images which can be associated with the documents.

Each document can have on of three image types associated with it - an author photo, a publisher logo and a publication logo. There aren't actually that many different authors, publishers and publications in the database so it would be common for many documents to have the same author or the same publisher although authors sometimes write for different publications. In the current setup it's possible for a document to have more than one image of each type associated with it, which is undesirable - this needs to eliminated and we need to ensure that each document can only have one image of each type associated with it.

It'd also be a huge boon if I could get all the information about a document and it's attached images with a single database call. In the current setup, trying to do this returns multiple rows so I've ended up making two calls, one for the document and one for it's attached images.

I can see two possible ways of setting this up, neither of which appeal.

The first is to add three columns to my document table to record the image URLs which is simple but inflexible should I want to add more image types later on.

The second is to set up three new tables for author, publisher and publication and associate each of these with the document table through a key and then to the image table through another key. There's also an image type table which is going to come in to play. I'm unwilling to do this because it's going to just explode the number of tables I've got, especially if we add more image types, and play havoc with my XML schemas.

Is there another way of rationalizing this that I've missed?

Cheers,
Matt

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved