Which is the best database schema for my navigation? [closed]

Question

I'm creating a web site where all pages hang off a database-driven tree-hierarchy.

All but one node has a parent node. Nodes may have role-based read permissions. Some nodes may have special rules (such as: don't display within navigation menus).

Nodes may represent links to other nodes (like a shortcut in Windows). Nodes typically represent pages.

Pages present either HTML content or execute programming. Some pages may be roots of subtrees (alternate masterpages and stylesheets).

Please help me setup my nodes database in Microsoft SQL Server for use by Linq to SQL.

I've got three ideas:

1. Many lightweight tables with almost zero nullalbe fields.

2. Heavyweight Node table with lots of nullalbe fields.

3. Best (or worst) of both: Lots of nullalbe foreign keys to many lightweight tables.

Which do you feel best represents the data? Which will be easiest to use with Linq to SQL?

How can I keep my data integrity rules within the database? How do I best enforce them within my programming?

• Nodes must be either (but not both) links or pages.

• Pages must be either (but not both) html or code.

• Links may not be roots, html, nor code.

Can I make an ASP.NET Site Map Provider with such a structure? Should I?

---

Update: I've asked a more general question:

What’s the best way to handle one-to-one relationships in SQL?

---

Related question:
How do I enforce data integrity rules in my database?

Falconry

Answer 1

My initial impression after reading your post is that I would be very averse to let any one technology (in this case linq) heavily influence the database schema design to the extent you seem to be suggesting.

I think your schema should be pretty much the same, regardless of what technology you then chose to build your business/presentation layers.

I hope I haven't misunderstood you.

Constantan