8

I'm new to database design, please give me advice about this.

1 When should I use a composite index?

im not sure what does index does, but i do know we should put it when it will be heavly loaded like for WHERE verified = 1 and in search like company.name = something. am i right ?

2 MySQL indexes - how many are enough?

is it just enough ?

3 Database Normalization

is it just right?

alt text

Thanks.

edit*

rules.

  1. each users( company member or owners ) could be a member of a company
  2. each company have some member of users.
  3. there are company admins ( ceo, admins) and there are company members ( inserts the products )
  4. each company can have products.

for the number 3 i will add a bit at users_company - 1 is for admin - 0 is for members

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
  • Problem with this is that we see the tables you've designed, but don't know anything about what rules there are to the data you'll be storing in them. – OMG Ponies Jul 22 '10 at 04:55
  • 1
    What if a company has mulitple 'Phones','Addresses' or 'Emails'? May want to make a new table CompanyContact and link to a Company, thus allowing multiple contacts for one Compony. Well that's my 2c anyway – PostMan Jul 22 '10 at 05:00
  • well the members or owners from the company will be the multiple contacts for the company, how about that ? –  Jul 22 '10 at 06:55

2 Answers2

4

Looks good, well normalised, to me at least.

I notice that each product can only belong to one company. If that's what you intended that's fine, otherwise you could have product have its own PID and have a product_company relation table, which would let more than one company sell a particular product. Depends who administers the products I guess.

I did notice that the user table is called 'users' (plural) and the others are singular ('company', 'product'). That's only a minor thing though.

thomasrutter
  • 114,488
  • 30
  • 148
  • 167
  • anyway i got a problem now, how can i insert a new company ? where should do i get the CID before im inserting the CID ? –  Jul 28 '10 at 09:00
0

The only comment I have is that you may want to consider just adding a mapping_id column to your users_company table and making CID and UID foreign keys, and add a UNIQUE constraint.

This way you can have a distinct Primary Key for records in that table which isn't dependent on the structure of your other tables or any of your business logic.

kdmurray
  • 2,988
  • 3
  • 32
  • 47
  • I wouldn't think this is necessary; how would having CID and UID as the primary key depend too much on the structure of other tables? Wouldn't adding another key de-normalise it to some extent? – thomasrutter Jul 22 '10 at 05:01
  • This is to some degree an "opnion" question. I suppose it depends on the context of the project and whether the CID and UID are exposed as "business data". A table's key should be an internal-only piece of data that's simply used to key records in a table. Multi-field keys can also make for more complex logic when working with the table. – kdmurray Jul 22 '10 at 05:11
  • hmm i so would like to do that, but they do have limits ( mapping_id as a int ), and do i have to add unique to each PK ? or PK and unique are quite the same ? –  Jul 22 '10 at 06:40
  • 1
    PKs are by definition unique. If you have other fields that you'd want to keep unique you can add a unique constraint to those as well. – kdmurray Jul 22 '10 at 16:47