mysql database normalisation/modeling

Question

I'm trying to move my office into a database application. Here is what I got:
_{(source: phunkei.de)}

companies and individuals inherit (1:1 relation) from clients. That is because both of them can be a "client" and receive a bill and I would like to reference to a single table in orders. There is another relationship between companies and clients: a many to many. I hope you guys have some ideas for improvements, because I'm not really sure of this.

edit: companies and individuals will have some different fields ...

Why do companies and individuals have relation between them? Just trying to understand better. Many to many relationship usually gets resolved by adding a table that contains only two keys of two tables i.e it will contain companies_id and individuals_id in it. — Vladimir Bozic
– Vladimir Bozic, Commented Feb 23, 2013 at 15:03
Yes there will be another table between them. Well there can be many individuals in a company I have to deal with. Sometimes a person can also be related with more than one company, so that's why there is a relation. The thing I find "unpretty" is the inheritance. — Daniel
– Daniel, Commented Feb 23, 2013 at 15:12

UberNubIsTrue · Accepted Answer · 2013-02-23 15:06:22Z

1

I consider myself to be a database newbie, so take this suggestion with a grain of salt. I would personally be tempted to set this up as a star schema. Here is an example from:

Microsoft Technology: BI Dimensional Model - Star Schema

star schema

This should allow for quick query speeds (if everything is indexed correctly) and should allow you to easily add on more attributes to query. One trade-off for quick queries is updating the Fact table. This will be slower due to indexing.

This has worked well for me in past projects but, again, I am by no means a qualified database professional.

answered Feb 23, 2013 at 15:06

UberNubIsTrue

6426 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Daniel Over a year ago

thanks for this example. But my problem is, that I have 2 different kinds of customers (companies and individuals).

UberNubIsTrue Over a year ago

Could you use companies/individuals as an attribute of customer id, instead of creating a separate table for each?

Daniel Over a year ago

that was my first design. But to keep it flexible and valid to possible extensions, i decided for the inheritance. But I will mockup both now and do some tests. Thanks.

UberNubIsTrue Over a year ago

Sounds good, let us know how it goes. Just one other note around flexibility and extensions. You can use Date_To, Date_From, and Current_Record fields on your Dimension Tables (these are all the tables joined to Sales_Facts in the table above). This will give you historical reporting capabilities (google: slowly changing dimensions for more info). And avoid snowflaking (google this too)! Okay, I'm done rambling, best of luck!

Collectives™ on Stack Overflow

mysql database normalisation/modeling

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related