I'm waiting on a meeting to discuss my conclusion that SharePoint is the wrong platform and then hopefully choose another platform I suggested. While I'm waiting I've decided to go ahead with designing the database. So much stuff has come flooding back on databases I haven't done since first year.
To design the database I am modelling it on the ANSI/SPARC architecture. This architecture has been around for years and it is tried and tested. It allows the views of the database to be separated from the actual physical representation. The external level in the architecture allows different users to have a different view of the database e.g. admin will have a different view from a normal user. The internal level is how the data is actually stored where it is concerned with disk space, indexes, compression, security etc.
When designing the database the first part that is considered, and my case is no different, is the conceptual level where entities, relationships, constraints etc. are all planned out. I am using the following process to design this conceptual level;
Determine the purpose of the database
Find and organise the information
Divide the information into tables
Divide information in the tables in columns (attributes)
Set primary keys for each table
Identify relationships (cardinality)
Refine the design (populate with test data and run queries then adjust design according to tests i.e. add another table or attribute to a table etc.
Apply normalisation rules
Database Design Programs
In my search for free ER diagram tools I stumbled upon a program called RISE, - http://www.risetobloome.com/Page_1_S.aspx?ITEM=1853 as the computer in the office here only has a watered down version of Microsoft Visio that doesn't have the option to create ER diagrams. Anyway the program is good, got the job done and I produced two ER diagrams from it that the boss was happy with. Then I came across MySQL Workbench - http://wb.mysql.com/ which I've never seen before, since I haven't designed a database in ages. It's even better than RISE because I've created the database from an ER diagram without the need to code each and every SQL table and relationship etc. etc. You can forward engineer your diagram and the database goes right into a selected MySQL database. What more do you want. You can also perform CRUD operations on the database from Workbench. It is a great tool and I'm really happy with it.
In my search for free ER diagram tools I stumbled upon a program called RISE, - http://www.risetobloome.com/Page_1_S.aspx?ITEM=1853 as the computer in the office here only has a watered down version of Microsoft Visio that doesn't have the option to create ER diagrams. Anyway the program is good, got the job done and I produced two ER diagrams from it that the boss was happy with. Then I came across MySQL Workbench - http://wb.mysql.com/ which I've never seen before, since I haven't designed a database in ages. It's even better than RISE because I've created the database from an ER diagram without the need to code each and every SQL table and relationship etc. etc. You can forward engineer your diagram and the database goes right into a selected MySQL database. What more do you want. You can also perform CRUD operations on the database from Workbench. It is a great tool and I'm really happy with it.
Issues
A couple of things came up during the design, one of which was the many-to-many relationships. I've dealt with this by using junction tables to break up the relationships from a many-to-many to one-to-many or zero-to-many. A good example, that I'm going to rob but it's probably been used hundreds of times anyway, is the situation of the orders table and the products table. One order can contain many products. One product can appear on many orders. This causes the problem of where to put the foreign keys to link these two tables without getting duplicate information. The pk ProductId as a foreign key in the orders table would mean that for more than one product per order there would be more than one record on the orders table. The pk OrderId as a foreign key in the products table would mean more than one record in the products table for each product. So the junction table called OrderDetails would take both the ProductId in the products table and the OrderId in the orders table as foreign keys to form a primary key. This solves the problem of the many-to-many relationship and the picture below illustrates the point.
A couple of things came up during the design, one of which was the many-to-many relationships. I've dealt with this by using junction tables to break up the relationships from a many-to-many to one-to-many or zero-to-many. A good example, that I'm going to rob but it's probably been used hundreds of times anyway, is the situation of the orders table and the products table. One order can contain many products. One product can appear on many orders. This causes the problem of where to put the foreign keys to link these two tables without getting duplicate information. The pk ProductId as a foreign key in the orders table would mean that for more than one product per order there would be more than one record on the orders table. The pk OrderId as a foreign key in the products table would mean more than one record in the products table for each product. So the junction table called OrderDetails would take both the ProductId in the products table and the OrderId in the orders table as foreign keys to form a primary key. This solves the problem of the many-to-many relationship and the picture below illustrates the point.
Foreign key problems also occurred mainly in the form of the error -
errno:121 (Duplicate key) with CREATE TABLE
or
ERROR 1005 (HY000): Can't create table '.' (errno:121)
This means that foreign keys must be unique to the database, so you cannot have a foreign key in one table with the same name as a foreign key in another. One way I was going to check the foreign keys was to painstakingly review each one in the ER diagram. However, thanks to a blog I came across I used the following query to review the foreign key names;
SELECT
constraint_name,
table_name
FROM
information_schema.table_constraints
WHERE
constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
constraint_name;
constraint_name,
table_name
FROM
information_schema.table_constraints
WHERE
constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
constraint_name;
This query is taken from http://thenoyes.com/littlenoise/?p=81
I'm going to run some tests on the database with some queries and continue to improve it. Hopefully I get to sit down and discuss not using SharePoint and then all I have to do is rename this blog!?
