Database design (MySQL)

[greggpb]

You could also go super generic and implenent a EAV model
like Mr JAx has.

EntityType(ID, Name)

Entity(Id,EntityTypeID)


EntityRelationship(ID,EntityID, EntityID)

EntityRelationshipValue(EntityRelationshipID,Value)


I built database's like this for finaicial institues for 8 years.. they are very compact and work really quickly..

 

[Mr.Jax]

You could also go super generic and implenent a EAV model
like Mr JAx has.

EntityType(ID, Name)

Entity(Id,EntityTypeID)


EntityRelationship(EntityID, EntityID)

Yup yup + 1

 

[Other Pineapple Smurf]

You could also go super generic and implenent a EAV model
like Mr JAx has.

EntityType(ID, Name)

Entity(Id,EntityTypeID)


EntityRelationship(ID,EntityID, EntityID)

EntityRelationshipValue(EntityRelationshipID,Value)


I built database's like this for finaicial institues for 8 years.. they are very compact and work really quickly..

2nd that approach


BTW: Apply both methods so you can understand them. In the real world this is used every day.

One last thing, your never to old or too young to learn new things in SQL. Always ask questions and always try something new. Have fun!!!

 

[thesadguy]

You could also go super generic and implenent a EAV model
like Mr JAx has.

EntityType(ID, Name)

Entity(Id,EntityTypeID)


EntityRelationship(ID,EntityID, EntityID)

EntityRelationshipValue(EntityRelationshipID,Value)


I built database's like this for finaicial institues for 8 years.. they are very compact and work really quickly..

I'm slightly confused WRT how EntityRelationship and EntityRelationshipValue fit into this.

I'm guessing EntityRelationship defines relationships between two entities. But how does the Value field in the EntityRelationshipValue table fit in?

 

[greggpb]

// They Types of entities
EntityType(ID, Name)
1,Person
2,City
// you can have data on a here by Adding fields I.e Name
Entity(Id,EntityTypeID, Name)  
1,1,Gregg 			// There is a person called Gregg
1,2,Cape Town		// City Called Cape town


// on this table there should be some sort or relationship type 
// and you should figure out if the relationship is directional
EntityRelationship(ID,EntityID, EntityID)
1,1,2 				// Gregg is related to Cape town in some way

// here you can put a value on the relationship between Gregg And Cape Town
// the data is only valid when gregg was living in cape Town
// you need to put in a value type so you know its an address

EntityRelationshipValue(EntityRelationshipID,Value)
1,"10 Main Street"  //  when gregg was in cape town the value "10 main street" is assosated with hime

 

[thesadguy]

// They Types of entities
EntityType(ID, Name)
1,Person
2,City
// you can have data on a here by Adding fields I.e Name
Entity(Id,EntityTypeID, Name)  
1,1,Gregg 			// There is a person called Gregg
1,2,Cape Town		// City Called Cape town


// on this table there should be some sort or relationship type 
// and you should figure out if the relationship is directional
EntityRelationship(ID,EntityID, EntityID)
1,1,2 				// Gregg is related to Cape town in some way

// here you can put a value on the relationship between Gregg And Cape Town
// the data is only valid when gregg was living in cape Town
// you need to put in a value type so you know its an address

EntityRelationshipValue(EntityRelationshipID,Value)
1,"10 Main Street"  //  when gregg was in cape town the value "10 main street" is assosated with hime

Thanks! I get it now!

One thing, in the Entity table, shouldn't the first field's value of the second record be "2", since it's the EntityID?

 

[greggpb]

yeah.. my auto number doesn't work so early in the morning

 

[ichigo]

people live in many cities and cities have many people so with relationship like this you do need a linking table for it to work multiple - multiple. only time you dont need a table is when its single - multiple relationship or single - single.

so in your case the linking reference table has primary key from each table to it. Remember primary keys musnt be ascending automatically it must be changable manually type havent databased for ages so cant remember the name.

So if i reference number 1 city cape town in primary key column cities to lets say person 22 graham smith in primary key column people it will work and link them together (all done on the relationship table/linking table)