[RESOLVED] Need help setting out a database

MattG

I will be using MySQL linked to PHP but I'm thinking of many different ways to achieve what I have to without settling on something that is good enough so I was wondering if anyone could offer there advice on a good way to set it out.

I am doing a website for a friend who is an agent, they want a website which lists all their clients (actors) and what they've appeared in.

I have one table which lists the people and all their details and another table that lists various shows\movies\commercials - that bit is fine.

The bit I'm struggling to come to a decision on is linking the two. There will be times when I need to be able to list multiple scenes or more than one appearence in the same thing and what happens in each scene/appearance.

I originally thought of an easy table with the fields personid and showid linking to the right person and show/movie etc. One entry for each person in the people table with the final field being a text box listing them all and exploding via the \n to show it on the site but that isn't exactly efficient.

I then thought about one with the above with a new entry for each credit but that could end up running into the 10's of thousands so I figured that also is probably not the best option.

Anybody got any ideas of a better way to do it? If what I've said doesn't make any sense let me know and I'll try to explain it a bit better. 😃

leatherback

Hi,

You have a typical many-to-many relational database here. The way I solve this is:

Table actors:
<id><name><age><hair><phone>

Table gigs
<id><name><url><year><type>

Table Gig_to_person
<gig_nr><person_nr>

You can then join the two tables where you can have multiple actors per gig, and multiple gigs per actor.

Sxooter

Note that while the intermediate table may grow quite large in terms of number of rows, if you're using ints in it, the actual on disk footprint will be very small, and with proper indexing the performance should be very good.

business1

That seems harder than you make it sends essay or you now how to come across thanks .

http://www.thebusinesssuccessgroup.com/Real-Estate-Investment-training.html

MattG

Sxooter wrote:
Note that while the intermediate table may grow quite large in terms of number of rows, if you're using ints in it, the actual on disk footprint will be very small, and with proper indexing the performance should be very good.

So if I use leatherback's suggestion of setting out the tables the performance should be very good as I am only including the int values to link to the other tables? Have I got that right?

Does this mean adding a text value to the "Gig_to_person" table to include details of the scene (small amount of text) would dramatically increase the workload?

Sxooter

nah. Basically, most dbs can efficiently join several tables worth of data about as fast as a single table. It's when you start joining lots of tables that you might have issues.

The main advantage here is that when you don't need to join the three tables together, your performance will be very good, and when they're joned together, you should get pretty decent performance, and you won't have a lot of dead fields in most of the rows of your db

MattG

Thank you both for the help.