Hi,

I have two questions.

1. Each video has information about its content (they are poker instruction videos, so it's fields like 'game_type', 'stakes', etc.) -- should I use two tables, or one?

One
video: id, name, content_game_type, content_stakes, etc. (I prefix with 'content' because a video in itself does not have 'stakes', for example.)

Two
video: id, name
video_content: video_id, game_type, stakes, etc.

2. These fields (game, game_type, stakes, table_size, tables) all have relatively static value sets, as follows:

Game: LHE, NLHE, Omaha 8/b, Omaha H/L, Other
Type: Cash, MTT, SNG, Special
Stakes: Lower (0.05/0.10 - 0.25/0.50), Middle(1/2 –3/6), Higher (5/10+)
Size: Long handed, Heads up, Short handed
Tables: 1, 2, 3, 4+

Should I use MySQL's ENUM type, or create lookup tables for each of these fields?

Thanks,

_da.

I don't know about MySQL's ENUM type so can't comment on its benfits. I would probably use lookup tabes anyway since these are standard and can be ported.


Whether or not you need one or more tables depends on the nature of the data. Can a video contain information on more than one combination of all these fields. For example, an NLHE MTT video might cover tables of 3 and 4+?

Hi,

1. True. It just seemed excessive to have so many small tables. Maybe I'm looking at it the wrong way?

2. Nope, just one set of fields per video. Use one table?

Thanks,

_da.

Tables represent entities. Some entities are information rich (lots of columns) some are information poor. Some can have, in theory, an infintite of instances (a row in the table is an instance of the entity) (order details for example), some have a very small and, broadly, fixed number of instances (your little lookup table here for example). But they are all entities. You generate your entities through a process of normalisation which is driven by dependencies. Whether or not this results in lots of little tables or several huge ones is irrelevent.

Tell you what - can you justify Two?

Thanks, that makes me feel a lot better about it.

It would only be for semantic reasons. i.e. Do these fields represent a separate entity?

_da.

And they don't: One-to-one relationship.

BTW - the stuff about the lookups. There is a counter argument that since these things consist of nothing more than lists of acceptable values (i.e. there are no non-key attributes in there) they do not represent entities in themselves. They are there to support correctness rather than integrity. You could instead just use check constraints in your table DDL.
The advantage of this - less tables. The disadvantages - adding a new game, for example, requires a DDL change; your app needs to maintain a seperate list for users to select from.

Not sure what you mean here -- do you mean between the video entity and each of the 'content' entities? (i.e. games, game_types, stakes, etc.)

Yeah, it's pretty much the same with ENUMs -- any changes you wish to make require changes to the DDL.

_da.

I was answering your question:
Do these fields represent a seperate entity? (BTW - it's columns or attributes not fields if you are being fussy - I usually am )
They don't represent seperate entities so one table.

Thanks so much for your help, pootle flump.

_da.