CODEWITHSUNDEEP
databasedatabase-designrelational-databasedatabase-schema
Pittfall
Pittfall

Reputation: 2851

Am I on the right track with this database design?

I have a design that I have set up and I could really use some help to see if I am on the right track. If this is not an appropriate question for Stackoverflow, I apologize and I will remove it. I'm a fairly new user and not 100% sure.

I am building a content manager and everything is coming dynamically from the database. I have a page that has sections and these sections have fields and data is entered into these field. The fields could be textboxes, comboboxes or listboxes. The point here is to be able to create pages, these pages get the appropriate sections with fields based on the section type and I simply want to store the values so that the page can be read or edited later.

Page1

Section1

field1 [Texbox1]
field2 [Texbox2]
field3 [Combobox1]

Section2

field1 [Texbox1]
field2 [Listbox1]
field3 [Combobox1]

The sections can be reused on different pages and the fields are always the same on every section.

My database looks like this:

Page
* PageId
* PageName

Page_Section
* PageId
* SectionId

Section
* SectionId
* SectionTypeId
* SectionName

SectionType
* SectionTypeId
* SectionTypeName

Field
* FieldId
* SectionId
* FieldName

FieldValue
* FieldValueId
* FieldId
* FieldValueValue
* Current Value (This is a bit because a combobox could have multiple values but I wanna show which one is selected. Similarly, a listbox could have multiple values selected)
* PageId

So a page is created with the sections based on a section type. Each section has x number of fields and then obviously each page field can have a different value that's why FieldValue has pageId but it feels weird to put in that pageId in FieldValue but I see no other way. Am I on the right track?

Upvotes: 1

Views: 168

Answers (3)

Jay
Jay

Reputation: 27474

My big question would be, What is it that you are trying to record? Is the point to record the aassignment of fields to sections and sections to pages? Or is the point to record business data that just happens to be split across multiple sections and pages because of limits of screen size, etc?

For example, suppose on page 1 we have, say, customer name and address, and on page 2 we have account number and phone number. If tomorrow someone were to redesign the screen to move the account number to page 1, then your present database would require major data updates to move all the field records for account numbers from page 2 to page 1. If this is important to whatever you want to do with the data, like if you want to query "get me a list of all the fields on page 3", then that's simply how it is, and your design here is a good one. But if the association of fields to pages is just a coincidental fact, and not something you would care about in queries, then you're heading in the wrong direction. If that's the case, I would leave out everything about pages and sections and instead think of the business objects about which you are collecting data, like customers and stores and products or whatever it is.

That is, is this a "screen layout database", or is it a "customer information database" or "product database" or some such? If it's a screen layout database, then you're on the right track. If it's a product database, then you're on the wrong track.

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753695

I think you're on the right general track.

One area I'd worry about is the sequencing of sections on each page — I think your Page_Section table needs an extra column SeqNo SMALLINT NOT NULL which takes values in increasing order (they don't have to be contiguous), so that when you order by SeqNo, your sections will be presented in the correct order on the page.

You might need a similar sequencing column for fields within a section.

Your Field table will probably need some field type information.

Maybe your FieldValue table should be renamed to PageFieldValue; it is designed to associate a value with the field on a particular page. As long as a given field can't appear more than once on a page, the renamed table is accurately named; it contains the value of a field on the nominated page. It isn't clear whether there can be some sort of default system so that if a page doesn't specify a particular value for a field, then the default value kicks in. It might be worth thinking about.

Upvotes: 2

sPaceVodka
sPaceVodka

Reputation: 31

Depending on the applications, if a section has fixed fields and is frequently accessed, you can perform denormalization for that section to improve the query performance. In a way, you can create a table section_x:

section_x
field_1
field_2
...
field_n

You can find more about denormalization on this wiki page: https://en.wikipedia.org/wiki/Denormalization

Upvotes: 0

Related Questions