What is the best way to model this data? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

What is the best way to model this data?

Author

Topic

fr0id
Starting Member

4 Posts

Posted - 2009-10-11 : 23:41:40

I'm trying to model the relationship between two objects: Persona and Fusion.

The basic schema for a Persona is as follows:

PersonaID (PK)
Name
Description

A Fusion is composed of the sum of 2 or more Persona's. Below is some sample data:

Persona: Unicorn
Fusions:

Orpheus + Yomotsu Shikome
Orpheus + Naga
Slime + Yomotsu Shikome
Nekomata + Gurr
Apsaras + High Pixie
Apsaras + Sarasvati

Persona: Orpheus Telos
Fusions:

Thanatos + Chi You + Helel + Asura + Messiah + Metatron

From an OOP perspective, I'm viewing this as a Persona having a collection of Fusions and a Fusion having a collection of Persona's (that doesn't include the Persona to which it belongs to), but I'm not sure how to go about modeling the Fusion table.

Some ideas running around in my head are:

FusionID (PK)
FusedPersonaID
PersonaID_1
PersonaID_2
PersonaID_3
PersonaID_4
PersonaID_5
PersonaID_6

where all columns to except FusionID are FK's to the Persona table. I don't really like this design as something tells me there is a better way to model this.

Another idea is:

FusionID (PK)
FusedPersonaID
FusionXml

What would be the best way to go about modeling this relationship?

dportas
Yak Posting Veteran

53 Posts

Posted - 2009-10-12 : 02:26:22

CREATE TABLE FusionPersona
(FusionID INT NOT NULL REFERENCES Fusion (FusionID),
PersonaID INT NOT NULL REFERENCES Persona (PersonaID),
PRIMARY KEY (FusionID, PersonaID) );

fr0id
Starting Member

4 Posts

Posted - 2009-10-14 : 23:06:30

quote:
Originally posted by dportas

CREATE TABLE FusionPersona
(FusionID INT NOT NULL REFERENCES Fusion (FusionID),
PersonaID INT NOT NULL REFERENCES Persona (PersonaID),
PRIMARY KEY (FusionID, PersonaID) );

What does the schema of the Fusion table look like? Or did you mean to type:

(FusionID INT NOT NULL REFERENCES Fusion (FusionID)

as

(FusionID INT NOT NULL REFERENCES FusionPersona (FusionID) ?

Your suggestion of using self-referencing keys did open up my mind to other possibilities.

dportas
Yak Posting Veteran

53 Posts

Posted - 2009-10-14 : 23:13:59

quote:
Originally posted by fr0id

What does the schema of the Fusion table look like?

It would contain any attributes of the Fusion. A name for example? If there aren't any other than FusionID then manybe you don't need the Fusion table at all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources