CODEWITHSUNDEEP
mysql
AlexWerz
AlexWerz

Reputation: 739

MySQL: Hundreds of tables or one big table?

I want to create a webpage where user can organize things they collect. As everyone collects something else I want to have the users create their own datamodell (with strict limitations). If two people are collection the same "things" they can share a datastructure. My idea was to give every collection an ID and all the tables belonging to that collection will have the ID as a prefix.

Table: Collections
ID | Collection
 1 | Poststamps
 2 | Barbie Dolls

Table: 1_Base
ID | StampValue | StampPic
....

Table: 2_Base
ID | EAN | Pic
....

Thus I would create many tables as each user could in theory create their own collection. I could also use only one very big table and a mapping table. Example:

Table: Colleactions
ID | Collection
 1 | Poststamps
 2 | Barbie Dolls

Table: Mapping
fkCollection | FieldName    | Mapping
1            | DoubleField1 | StampValue
1            | BlobField1   | StampPic
2            | StringField1 | EAN
2            | BlobField1   | StampPic

Table: CollectionData
fkCollection | DoubleField1 | ... | DoubleField10 | StringField1 | ... | Stringfield10 | BlobField1 | ...
1            | 30           |     |               |              |     |               | ...        |
2            |              |     |               | 21312412414  |     |                | ...        |

Any other ideas?

Thanks for your help!

Upvotes: 0

Views: 99

Answers (1)

5tar-Kaster
5tar-Kaster

Reputation: 908

From what I can see, your second way of attempting this is going to be the easiest way... your queries will be ten fold simpler to handle, and you wouldn't need to programmably create tables on the fly... so my suggestion would be to modify your second idea slightly... Just to clarify something also, A blob will slow down the query speed so I am changing the block to hold the source link to the image instead.

TABLE: Collections

ID| Collection
1 | Poststamps
2 | Barbie Dolls

Table: CollectionData

fkCollection | DataType    | VALUE  | FieldName  |
1            | Double      | 30     | StampID    |
1            | String      | London | StampName  |
1            | ImgSrc      | ../loc | StampPic   |
2            | String      | Ken    | BarbieName |
2            | ImgSrc      | ../loc | BarbiePic  |

Upvotes: 2

Related Questions